SOLVED

Slightly head melting

Brass 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.

best response confirmed by Alison Flynn (Brass Contributor)
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

1 best response

Accepted Solutions
best response confirmed by Alison Flynn (Brass Contributor)
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))))))
)

 

View solution in original post