Linked Nested IF formula returning 0 and not blank

**the3rdman77** · 10-25-2012, 05:50 AM

Hi

I have been looking everywhere fore a solution to this and I am sure I have been close but I just can't see the wood for the trees.

Here goes

I have a nested IF formula that is working from what value is returned in a different cell, but if no data is in the cell it returns as 0 and not blank. I have tried "" but I must be doing something wrong.

IF(AND(A1="",B1=""),"",A1+B1) = answer in Cell C1 and blank if there is no data in A1 and B1.

Nested IF

In D7 if(C1=0,10%,IF(C1=1,5%,IF(C1>1,0%)))

So as you can see I need the formular to recognise 0 but not blank cells.

Please help

Phill

**Pete_UK** · 10-25-2012, 05:53 AM

Put this in D7:

=IF(C1="","",if(C1=0,10%,IF(C1=1,5%,IF(C1>1,0%))))

Hope this helps.

Pete

**the3rdman77** · 10-25-2012, 06:05 AM

Thanks Pete

It works just fine - I cant believe it was only one quotation mark!

Thanks again

**TMS** · 10-25-2012, 06:09 AM

Are you ever likely to get a negative number in cell C1? If you do, the formula will return FALSE as you do not cater for that condition.

If it can never be less than zero, not a problem. Might be worth noting for future reference though.

Regards, TMS

**Pete_UK** · 10-25-2012, 06:30 AM

Originally Posted by the3rdman77

It works just fine - I cant believe it was only one quotation mark!

If you link to another cell (eg =C1) and that cell is empty, then Excel will treat it as being zero, so if you want a blank returned instead then you have to do:

=IF(C1="","",C1)

Now if C1 contains zero that will be returned correctly.

Hope this helps.

Pete

P.S. You might like to mark the thread as Solved if you consider it to be so (the FAQ describes how).