Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
zero denominator
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
zero denominator
=SUMIF(A1:C1,"<1E100")
"Amanda" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
zero denominator
"Amanda" wrote:
I have a problem to add numbers with cells that has error. Any advice what can be done? 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 ----- "Amanda" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
zero denominator
Hi Joe & teethless mama,
Thanks. This helps. Also will note the p/s. Thanks heaps! "JoeU2004" wrote: "Amanda" wrote: I have a problem to add numbers with cells that has error. Any advice what can be done? 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 ----- "Amanda" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
zero denominator
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Amanda" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Date/ and find most common denominator. | Excel Worksheet Functions | |||
Can I turn off 0 divide error, for denominator = 0 | Excel Discussion (Misc queries) | |||
lowest common denominator | Excel Worksheet Functions | |||
How to cause the value in the denominator change with changing row | Excel Discussion (Misc queries) | |||
specify denominator in fraction | Excel Discussion (Misc queries) |