#DIV/0! error when using COUNT

R

Randy

Would like to have help eliminating the "#DIV/0!" error appeaing in a cell.
Here is a sample of the data:
A B C D E
1 Sample X val Y val Difference Formulas in
Column D
2
IF(A2<>"",B2-C2,"")
3
IF(A3<>"",B3-C3,"")
4
IF(A4<>"",B4-C4,"")
5
IF(A5<>"",B5-C5,"")
6
IF(A2<>"",SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5)

Issue: When the table is blank cell D6 has the error #DIV/0!
Question: How can formula in D6 be modified so when table is blank that D6
is also blank

Thank You
Randy
 
E

Eduardo

Hi
try

=IF(A2<>"",if(iserror(SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5),"",SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5)))
 
E

ellen

If you copied a formula down the entire column, then the cell is trying to
operate on 0. remove the formula.
 
S

Shane Devenshire

Hi,

My question is what does checking A2 do? If A2 is blank but the rest of the
table is not your formula will not calculate?

My tests don't seem to validate

=IF(A2<>"",IF(ISERROR(SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5),"",SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5))

suppose the only cell with contents A2 and A5:C5. since B2:C2 are blank D2
is 0, and if A5:C5 contain Hat, 7, 8 then D5 returns -1 and the fromula
returns 0.5, but I think it should be 1?

You can make this calculation a lot simplier if you change the formulas in
D2:D5 to
=IF(A2<>"",B2-C2,0)

Then your formula is the array entered:

=IF(COUNTIF(D2:D5,"<>0")=0,"",SUM(ABS(IF(D2:D5<>0,D2:D5,0)))/COUNTIF(D2:D5,"<>0"))

Press Shift+Ctrl+Enter to enter it. If the display of 0 in D2:D5 bothers
you, you can suppress it with a custom format or conditional formatting.
 
R

Randy

Eduardo,
Thank you, this worked.

Randy

Eduardo said:
Hi
try

=IF(A2<>"",if(iserror(SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5),"",SUM(ABS(SUMIF(D2:D5,{">0","<0"}))))/COUNT(D2:D5)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top