Average Cells above 0

S

Stephen

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!
 
R

Ron P

Stephen said:
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!
Click to expand...

Try this

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

B. R.Ramachandran

Hi,

Use the following formula,

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

Regards,
B. R. Ramachandran
 
R

Ron Rosenfeld

Hi,

Use the following formula,

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

Regards,
B. R. Ramachandran
Click to expand...

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
 
B

B. R.Ramachandran

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
 
Top