#DIV/0! Errors

[Carl Clements]

Hi,

I'm using a long formula which is returning a #DIV/0! value for some cells where it is unable to perform the calculation.

Is there a subsequent formula I can use to change this to a different value? I tried using the below IF statement but it still returns the same message.

=IF(S9="#DIV/0!",1,2)

Thanks,
Carl

 

[Richard Schollar]

You could use:

=IF(ISERROR(S9),1,2)

or if you want to be completely specific about #DIV/0!:

=IF(ERROR.TYPE(S9)=2,1,2)

or just fix the original formula

 

[JazzSP8]

Hi Carl

If your using Excel 2007 then you can use something like

=IFERROR(S9,1)

Or an earlier version...

=IF(ISERROR(S9),1,2)

HTH