SOLVED
Home

Slightly head melting

Alison Flynn
Contributor

Hi guys,

 

I've a perfectly working formula that returns a value of either 0 or 1 depending on criteria: at risk = 1 or not at risk = 0.

 

However, if cell AM2 in particular is left blank it's returning a value of 1 as it's <1.04 therefore a 1 value.

 

Here's the formula:

=IF(AI2>5.18,1,IF(AK2>3.37,1,(IF(AM2<1.04,1,IF(AP2="Yes",1,IF(AP2="No",0,0))))))

The question I have is:

Can the formula be constructed to leave the cell blank if there is no entry in cell AM2? And if so, how to do it?

Hope all that makes sense!

Thanks in advance.

 

7 Replies

It could be

=IF(AI2>5.18,1,IF(AK2>3.37,1,(IF(LEN(AM2)=0,"",IF(AM2<1.04,1,IF(AP2="Yes",1,IF(AP2="No",0,0)))))))

Thanks Sergei,

 

I seem to be keeping you busy!  

 

Afraid that didn't do it...

Alison 

Alison, you'd like to keep it empty always when AM2 is empty (i.e. independently on AI2, AP2, AK2) or only when AI2<5.18 or AK2 < 3.37?

Yes to have it empty when AM2 is empty.  In saying that, if AM2 is empty all of them will be empty.

Solution

When you may wrap your formula by checking AM2 like

=IF(LEN(AM2)=0, "",
  IF(AI2>5.18,1,IF(AK2>3.37,1,(IF(AM2<1.04,1,IF(AP2="Yes",1,IF(AP2="No",0,0))))))
)

 

Worked perfectly!

 

Thanks a million Sergei

 

Alison

You are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies