If you enter the formula in the same row that also contains data referenced
in the formula then you get a result due to the rule of implicit
intersection. This result will more than likely be incorrect but if does
appear to be correct it's probably just a coincidence.

Here's a screencap of a simplified example:

http://img398.imageshack.us/img398/6312/arrayfn9.jpg

Where the formula is not array entered and is on the same row as the
referenced data it is evaluating the *single cell* that resides within
implicit intersection.

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
Yes, you are right it is. When I wrote it I tested it with a column of
calculated data and it appeared to work without being array entered, which
surprised me, so I did not add the *array formula* rider.

Testing it further, it seems that if the data is an arithmetic
progression, (which all of my samples were), it returns the apparently
correct answer.

However, even with random data I still don't get the #VALUE! error that
igcrews reported, I get a wrong answer, which is even worse! Maybe it is
a version thing.

mmmm..... I'm going to have to be more careful with my testing.
Complicated this stuff isn't it? <g

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
Sandy's formula is an array formula and needs to be array entered the
same as Mike's.

--
Biff
Microsoft Excel MVP


"jgcrews" wrote in message
...
Sandy:

I tried your suggestion and ended up with "#Value!" instead of an
average.

I then tried Mike's suggestion and it worked OK.

Me and my diabetes thank you both for your help!

Jeff

"Sandy Mann" wrote:

One way:
=AVERAGE(IF((MOD(ROW(L11:L59),8)=3)*L11:L59<0,L11 :L59,FALSE))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"jgcrews" wrote in message
...
I'm a relative novice and have a straight-forward question concerning
averaging. I need to average a specific set of cells (not a
continuous
range) and ignore any cells with a value of 0.

I'm sure this is pretty easy but the more I read about all of the
averaging
functions, the more confused I got.

Currently, I'm simply using:
=AVERAGE(L11,L19,L27,L35,L43,L51,L59)
which obviously isn't what I really want.

Can someone head me in the right direction? Thanks in advance for
your
help.

Jeff