IF ISERROR Problem

  • Thread starter Thread starter Alby
  • Start date Start date
A

Alby

Hi all,

Wondering if any of you gurus out there could help me.

I have a large spreadsheet, that I will be sharing with
my team and will therefore protect certain cells that are
the result of their data imput.

The problem I have is that I have copied all the formulas
down the pages and set conditional formats to blank out
any nil values. (so that it looks neat). However in the
last column I have the % margin:
Revenue - Cost / Revenue
As you all know if we do not have data in the cells that
this cell is dependant on we get a #DIV/0!.

I have tried the conditional format, but no joy.

Also tried :=IF(ISERROR(F19-I19/F19),"",F19-I19/F19)
F19=Revenue I19 =Cost

I'm sure it's something to do with the brackets, but for
the life of me I cant find.

Thanks in advance

Alby
 
Alby,

I tend to shy away from the ISERROR approach with #DIV/0
Just set up your formula to test for the zero.

Your formula:
=IF(ISERROR(F19-I19/F19),"",F19-I19/F19)

Check for the zero:
=IF(F19=0,"",F19-I19/F19)

Looking at this more closely, your formula should work too??

John
 
The DIV/0 error is because the equation is trying to divide by zero.
Alas, use the IF to check for a 0 in the denominator.

=IF(F19=0,"",F19-I19/F19)

You can also just say if F is blank, or I is blank or both.

=IF(ISBLANK(F19),"",F19-I19/F19)
=IF(ISBLANK(I19),"",F19-I19/F19)
=IF(AND(ISBLANK(F19),ISBLANK(I19)),"",F19-I19/F19)
,respectively.

- To
 
Back
Top