 • 511K Members
• 6,056 Online
• 608K Conversations
SOLVED

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!

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