Ignoring #DIV/0! in a formula
 

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

=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

DLZ217 wrote:

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

"Jerry W. Lewis" wrote...
=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)
....

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.