Ignoring #DIV/0! in a formula

D

DLZ217

Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried
messing around with IF statements but I cant seem to figure it out
 
J

Jerry W. Lewis

=IF(ISERROR(cellref),erroraction,noerroraction)
=IF(ISNUMBER(cellref),noerroraction,erroraction)

You can also use it in range functions, such as
=SUM(IF(ISNUMBER(range),range))
but you must array enter (Ctrl-Shift-Enter)

Jerry
 
H

Harlan Grove

Jerry W. Lewis said:
=IF(ISERROR(cellref),erroraction,noerroraction)
=IF(ISNUMBER(cellref),noerroraction,erroraction)

You can also use it in range functions, such as
=SUM(IF(ISNUMBER(range),range))
but you must array enter (Ctrl-Shift-Enter)
Click to expand...
....

Masking all errors is usually not a good idea. It's not all that difficult
to isolate just the #DIV/0! error.

=IF(COUNT(1/(ERROR.TYPE(x)=2)),error_return,nonerror_return)

=SUM(IF(ISERROR(1/(ERROR.TYPE(rng)=2)),rng))

Better still would be to trap the condition giving rise to the #DIV/0! error
values, such as trapping zero denominators or stats calculated from ranges
containing no numbers.
 
Top