Average a set of figures which ignores 0 entries
Thanks for your help- this also works. Very Clever !
"Ron Rosenfeld" wrote:
On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine"
wrote:
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.
Use this **array** formula:
=AVERAGE(IF(rng<0,rng))
(substitute your range to average for 'rng').
To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron
|