average formula help

[kevin carter]

We have a range of cells A1:A10 which contain values as follows:-

1 2 4 5 0 0 6 8 9 10

We need Cell A11 to display an average of A1 to A10 but ignoring the 0
values. Is this possible with a formula?


Thanks in advance

Kevin

 

[Paul B]

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

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[JE McGimpsey]

One way (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A10=0,"",A1:A10))

 

[Bob Phillips]

Hi Kevin,

Here's one way

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[kevin carter]

thanks both

One way (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A10=0,"",A1:A10))