Array Average

[tlynn]

I have been using this formula to average a range without averaging in the zeros:

{=AVERAGE(IF(A1:A10,A1:A10))}

to do some averaging..obviously..and have recently ran into a problem. When there is nothing yet in the range to average, (all zero's for a month that has not happened yet), then I get a #DIV/0!. Normally this would not be a huge problem using:

=IF(ISERROR(BLA, BLA),"",(BLA, BLA))

but...this isnt working now either and it states that there is an error with the double quote area of the formula. Is there a different way to do this?

Any help would be appreciated and thanks in advance.

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,
   CHOOSE({1,2},
     0,
     AVERAGE(IF(A1:A10 > 0, A1:A10))))

 

[tlynn]

Bump...anyone got any ideas?

 

[Scott Huish]

Aladin gave you one.

 

[Scott Huish]

If all your values are greater than 0, here's another one:

=SUMIF(A1:A10,">0")/MAX(1,COUNTIF(A1:A10,">0"))