AVG Formula

[Steve]

I am looking to write an AVG function that doesn't count
cells with zero's in it. The information is all in one
column. Some cells just habve no info. Can anyone help?


Thanks,
Steve

 

[keepITcool]

Steve:
does this work for you?
=SUMIF(A2:A5,"<>0")/COUNTIF(A2:A5,"<>0")

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Steve]

my number come out way low.

-----Original Message-----
Steve:
does this work for you?
=SUMIF(A2:A5,"<>0")/COUNTIF(A2:A5,"<>0")

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >




.

 

[keepITcool]

hmm.. doesnt work great with empty cells, does it?
try:

{=SUM(A$1:$A$5)/COUNT(IF(A$1:$A$5=0,"",A$1:$A$5))}


it's an array formula:
enter without the {} but close with ctrl-shift-enter
you'll see the {} in the formula bar.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Ron Rosenfeld]

I am looking to write an AVG function that doesn't count
cells with zero's in it. The information is all in one
column. Some cells just habve no info. Can anyone help?


Thanks,
Steve

Use the array formula:

=AVERAGE(IF(rng<>0,rng))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. XL
will place brackets {...} around the formula.


--ron

 

[Jerry W. Lewis]

Or .../(COUTIF(range,">0")+COUNTIF(range,"<0"))

Jerry