excluding #DIV/0! in further calculations

[tom ossieur]

Hi,

I am doing some calculations in several steps. In some steps the result of
some cells is #DIV/0!

as a result the final result (STDEV) gives #REF!

how to exclude the cells that contain #DIV/0! so I get the result in the
last step, based on the cells that had a intermediate result? (without
adapting ALL intermdiate steps with IF etc)

so i am looking for a simple trick in the last step

Thanks!

tom

 

[Bob Phillips]

Use an array function akin to

=STDEV(IF(NOT(ISERROR(A1:E3)),A1:E3))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

I am doing some calculations in several steps. In some steps the
result of some cells is #DIV/0!

as a result the final result (STDEV) gives #REF!

The only way you get #REF! is if the #DIV/0! error screws up a
function that returns a range reference, e.g., INDEX, OFFSET or
INDIRECT. Why not tell us which?

how to exclude the cells that contain #DIV/0! so I get the result
in the last step, based on the cells that had a intermediate
result? (without adapting ALL intermdiate steps with IF etc)

If the #DIV/0! is screwing up intermediate function calls that should
be returning range references, there's no way to respond with anything
useful without knowing more details. If I'm right about the nature of
the intermediate functions, your choices are limited to returning
nothing when there are errors, so the final formula would look like

=IF(ISNUMBER(STDEV(whatever)),STDEV(whatever),"")

or trapping the #DIV/0! errors in the intermediate function calls.
Maybe there's a better way to handle this, but you'd need to provide
more details.

 

[Dave F]

When I see #DIV/0! errors in my spreadsheets, I trap those errors, to avoid
problems such as yours.

Assume I have the formula =A1/A2 and A2 can equal 0. I would trap the
#DIV/0! error by modifying the formula: =IF(ISERROR(A1/A2),"",A1/A2). Then
any formula which refers to this cell will not return #REF errors, because
the cell wouldn't contain the #DIV/0! error. What happens, then, is your
spreadsheets are much cleaner and more reliable.

Dave

 

[Harlan Grove]

When I see #DIV/0! errors in my spreadsheets, I trap those errors,
to avoid problems such as yours.

Assume I have the formula =A1/A2 and A2 can equal 0. I would trap
the #DIV/0! error by modifying the formula:
=IF(ISERROR(A1/A2),"",A1/A2)

....

But that traps any error in either cell A1 or cell A2. The only ways
A1/A2 returns #DIV/0! is if either A1 or A2 already evaluates to #DIV/
0! or A2 = 0. Usually a good idea to propagate UNEXPECTED errors and
only trap EXPECTED errors, so better to use

=IF(A2=0,"",A1/A2)

 

[Dave F]

Fair point.