Replace with "O' if #Div/0I

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aasreds - 23 Jun 2005 21:39 GMT

I am setting up a spreadsheet to calculate some figures for me and woul
like to set up the last column to display the results. Several of m
column are added together and the results then divided by a value (no
yet entered in the spreadsheet) resulting in the #Div/0! error. How d
I get the column to display "0" for the Div/)! error

Reply to this Message

swatsp0p - 27 Jun 2005 16:36 GMT

Place an IF statement to trap the error and return a zero, as such:

=IF(ISERROR(yourformula),0,yourformula)

Now, when your formula returns an error, zero will be the result
displayed.

HTH

Bruce

Signature

swatsp0p

Reply to this Message

JE McGimpsey - 27 Jun 2005 17:20 GMT

This works, but as an alternative, if your value is in a particular cell
(say, J100), use

=IF(J100=0, 0, SUM(A1:J99)/J100)

This is more efficient than calculating your formula twice, and has the
added benefit of not masking other errors the way ISERROR() does.

> Place an IF statement to trap the error and return a zero, as such:
>
> =IF(ISERROR(yourformula),0,yourformula)
>
> Now, when your formula returns an error, zero will be the result
> displayed.

Reply to this Message