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...

[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"))

[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)

"Ken D." wrote:

> 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...

[Ken D.]

Thanks Vito,

I used this solution and it worked terrific.

Thanks again...

"Vito" wrote:

>
> 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"))
>
>
> --
> Vito
> ------------------------------------------------------------------------
> Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
> View this thread: http://www.excelforum.com/showthread...hreadid=494713
>
>

[Ashish Mathur]

Hi,

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

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

Regards,

Ashish Mathur

"Ken D." wrote:

> 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...

[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