Division by Zero Error

[Ken D.]

I am using this formula to average the values in cells F49:J49 if the value
is greater than zero.

=SUMIF(F49:J49,">0")/COUNTIF(F49:J49,">0")

When all values are 0, I'm getting the division by zero error.

Just a little help please...

 

[bpeltzer]

You could put the entire average calculation inside an if that detects a 0
denominator and avoids the division in such cases:
=if(countif(F49:j49,">0")=0, 0, your average calculation)

 

[Vito]

Do you just want a blank cell if all cells are zero?

If so,

=IF(SUM(F49:J49)=0,"",SUMIF(F49:J49,">0")/COUNTIF(F49:J49,">0"))

 

[Ken D.]

Thanks Vito,

I used this solution and it worked terrific.

Thanks again...

 

[Ashish Mathur]

Hi,

You may use the following array formula (Ctrl+Shift+Enter):

=average(if((F49:J49>0),F49:J49))

Regards,

Ashish Mathur

 

[Vito]

Hi,

You may use the following array formula (Ctrl+Shift+Enter):

=average(if((F49:J49>0),F49:J49))

Regards,

Ashish Mathur

That stills gets you the #DIV/0 error message, which the OP was getting
originally, when all cells in the range are 0