How do I use an average function, not counting cells containing a

[Ryder]

I am working on setting up a spreadsheet that will have monthly data and from
time to time one of the months will have a zero value. Off of this data I
want to do an average, but not use the cells that contain zeros. This is a
monthly report and there are 90 of these monthly tables, so you can see why I
want to figure out how to automate this.

 

[Paul B]

Ryder, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<>0"))
--
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 2002 & 2003

 

[Adam]

Another way:
AVERAGE(IF(A1:A10<>0,A1:A10,FALSE)).
Enter as array formula i.e. CTRL+SHIFT+ENTER

"Ryder" skrev: