#DIV/0! error when using COUNT

[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(D25,{">0","<0"}))))/COUNT(D25)

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

 

[Eduardo]

Hi
try

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

 

[ellen]

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

 

[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(D25,{">0","<0"}))))/COUNT(D25),"",SUM(ABS(SUMIF(D25,{">0","<0"}))))/COUNT(D25))

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
D25 to
=IF(A2<>"",B2-C2,0)

Then your formula is the array entered:

=IF(COUNTIF(D25,"<>0")=0,"",SUM(ABS(IF(D25<>0,D25,0)))/COUNTIF(D25,"<>0"))

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

 

[Randy]

Eduardo,
Thank you, this worked.

Randy

Hi
try

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