Formula for average

[Kelly]

I need a formula to average the following.

A B C D
1 100 0 90 0 =95

I need it to average only numbers > 0.

 

[Frank Kabel]

Hi Kelly
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A11>0,A1:A1))

 

[Kelly]

If they all = 0 I get #DIV/0!. Is there any way to return
just 0?

 

[DDM]

Frank, a tiny correction in your formula (which I would never have spotted
if I hadn't tried it out):

=AVERAGE(IF(A11>0,A11))

 

[DDM]

Kelly, like this (entered as an array):
=IF(A11=0,0,AVERAGE(IF(A11>0,A11)))

 

[JE McGimpsey]

That will be equivalent to

=A1

unless A1 <=0, which will return #DIV/0

Try (array entered):

=IF(COUNTIF(A11,">0"),AVERAGE(IF(A11>0,A11)),"")

 

[Aladin Akyurek]

Needs just enter...

=SUMIF(A11,">0")/MAX(1,COUNTIF(A11,">0"))

If they all = 0 I get #DIV/0!. Is there any way to return
just 0?

[...]

 

[Frank Kabel]

Hi JE
not my evening
should have been
=AVERAGE(IF(A11>0,A11))

 

[Harlan Grove]

Kelly, like this (entered as an array):
=IF(A11=0,0,AVERAGE(IF(A11>0,A11)))

...

While that works with the sample data {100,0,90,0) in A11, what does this
formula give when A11 contains {0,100,90,0}? The first argument to IF is
ill-posed.