Dec 20 2018 08:53 AM
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.
Dec 20 2018 09:12 AM
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)))))))
Dec 20 2018 09:14 AM
Thanks Sergei,
I seem to be keeping you busy!
Afraid that didn't do it...
Alison
Dec 20 2018 09:27 AM
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?
Dec 20 2018 09:30 AM
Yes to have it empty when AM2 is empty. In saying that, if AM2 is empty all of them will be empty.
Dec 20 2018 09:33 AM
SolutionWhen 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)))))) )
Dec 20 2018 09:33 AM
SolutionWhen 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)))))) )