Division by Zero Error
 

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

Division by Zero Error
 

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

Division by Zero Error
 

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

Division by Zero Error
 

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

Division by Zero Error
 

Hi,

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

=average(if((F49:J490),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...

Division by Zero Error
 

Ashish Mathur Wrote:
Hi,

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

=average(if((F49:J490),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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=494713