Array formula

[Ray]

Hi,
I have a list of numbers and I want the average not
counting the 0.00 in the range.
a
12
15
19
0
101
150
0
52
67
122
Now my formula is
{=average(if($a$1:$a$10<>0,$a$1:$a$10,0))}
This will sum to 538 and average to 53.8
The result I am looking for is sum 538/8 or 67.25
Where is my error??
TIA
ray

 

[Bernie Deitrick]

Ray,

Leave off that last ",0": array enter (using Ctrl-Shift-Enter)

=AVERAGE(IF(A1:A10<>0,A1:A10))

HTH,
Bernie
MS Excel MVP

 

[Ron de Bruin]

This is not a array formula

=SUM(A1:A10)/COUNTIF(A1:A10,">0")