zero denominator

  • Thread starter Thread starter Amanda
  • Start date Start date
A

Amanda

I have a problem to add numbers with cells that has error. Any advice what
can be done? Thanks

A B C
a 5 5 5
b 1 1 0
a/b 5 5 #DIV/0! SUM A+B+C #DIV/0!
 
Amanda said:
I have a problem to add numbers with cells that has error.
Any advice what can be done?
Click to expand...

First, avoid the #DIV/0 error. Use one of the following formulas, based on
your preference:

=if(C2=0,"",C1/C2)

=if(C2=0,0,C1/C2)

I am assuming that the rows labeled "a" and "b" are rows 1 and 2.

Second, if you choose the first formula, compute the sum with SUM(A3,B3,C3)
instead A3+B3+C3, which would result in a #VALUE error because of the ""
text.

PS: In the future, it would be easier if you posted examples with actual
Excel row and column references instead abstract names like "a", "b" and
"a/b". "SUM A+B+C" is particular misleading.


----- original message -----
 
Hi,

And yet another way which may seem more intuitive:

=SUMIF(A1:C1,"<>#DIV/0!")

In the case of tm's formula the idea is to make the second argument larger
than anything in the range, so if you know you number will be smaller than
9,000,000,000 you can use:

=SUMIF(A1:C1,"<9E9")

or if you know the largest number will be 8 you could use

=SUMIF(A1:C1,"<9")

and so on.
 
Back
Top