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

Contributor

# Slightly head melting

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

# Re: Slightly head melting

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)))))))`

# Re: Slightly head melting

Thanks Sergei,

I seem to be keeping you busy!

Afraid that didn't do it...

Alison

# Re: Slightly head melting

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?

# Re: Slightly head melting

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

Solution

# Re: Slightly head melting

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))))))
)```

# Re: Slightly head melting

Worked perfectly!

Thanks a million Sergei

Alison

You are welcome