Average a set of figures which ignores 0 entries
Lorraine, 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
"Lorraine" wrote in message
...
I need to average the figures in several cells. However some cells have a
0
in them.
I therefore want the formula to ignore the cells which have a zero.
I have used the AVERAGE & AVERAGEA function, but both count 0 cells.
(although AVERAGEA ignores blank cells, I need to keep the 0s in as they
are
linked to another formula)
Thanks.
|