Average Cells above 0

[Guest]

Hi Everone! I'm trying to average a group of cells, but only the cells over 0.
I tried this - =average(if(i2515:i5043)>0,i2515:i5043)). I also entered it
as an array but it still didn't work. Please help!

 

[Domenic]

Try...

=AVERAGE(IF(I2515:I5043>0,I2515:I5043))

Hope this helps!

 

[Ron P]

Hi Everone! I'm trying to average a group of cells, but only the cells
over 0.
I tried this - =average(if(i2515:i5043)>0,i2515:i5043)). I also entered it
as an array but it still didn't work. Please help!

Try this

=sumif(i2515:i5043,">0",i2515:i5043)/countif(i2515:i5043,">0")

 

[Guest]

Hi,

Use the following formula,

=SUM(I2515:I5043)/COUNTIF(I2515:I5043,">0")

Regards,
B. R. Ramachandran

 

[Domenic]

The formula needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[Guest]

Thanks everone...it worked!
--
Thanks!

Stephen

The formula needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[Ron Rosenfeld]

Hi,

Use the following formula,

=SUM(I2515:I5043)/COUNTIF(I2515:I5043,">0")

Regards,
B. R. Ramachandran

Note that your formula does not meet the OP's request for "cells over 0".

Had he asked for "cells not equal to 0" then your formula would be appropriate.
However, your formula does not exclude those values "less than 0".


--ron

 

[Guest]

Hi Ron,
Thanks for pointing out the error in the formula. I mistook that the OP
wanted to average nonzero values, and realized the error later after posting
my reply!
Regards,
B. R. Ramachandran