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

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.

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


"Ryder" wrote in message
...
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.

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

"Ryder" skrev:

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.