Actually, seeing this, we can use the same principle I gave before, but
provide a much neater solution

=AVERAGE(IF(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1)) <0,N(OFFSET(D10:G18,{5,8,
0},{0,2,3},1,1))))

still an array formula, same rationale with the embedded constants arrays.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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