Question regarding averaging

[jgcrews]

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

 

[FSt1]

hi
is your data in a single column?
the average function takes in to acount all values in the range.
and broken up it looks something like.
=sum(L11:L59)/Count(L11:L59)
so you might break it up. instead of using the average function....
use this.
=sum(L11:L59)/Countif(L11:L59,">0")

if your data is not in a single column, the above could still be uses but it
would start looking more lilke the average example.

Regards
FSt1

 

[Mike H]

Try this

=AVERAGE(IF(N(OFFSET(L11,{0,8,16,24,32,40,48},0,1,1))<>0,N(OFFSET(L11,{0,8,16,24,32,40,48},0,1,1))))

It's an array so you must commit it with Ctrl+Shift+Enter and Excel will
place {} curly brackets around the formula

Mike

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Nice one Sandy. I had a 'Column' head on because the data are in a column and
should have realised your much simpler formula utilising ROW would work

Still, on the bright side mine would work if the data weren't evenly spaced
by tuning the offset.

Mike

 

[Sandy Mann]

Nice one Sandy.
Serendipity <g>

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[jgcrews]

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

 

[T. Valko]

Sandy's formula is an array formula and needs to be array entered the same
as Mike's.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

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.

 

[Sandy Mann]

Thank you vary much for taking the time to explain that Biff, it was vary
kind of you.

I have fallen foul of the *rule of implicit intersection* before when
failing to array enter an array formula so I should have been alert to the
problem. What was fooling me here was the fact that it was *not* returning
the implicit intersection, it was returning the correct answer regardless of
what Row I entered the formula in - provided that it was within the range of
the formula and the *all* the data was indexed at a constant rate.

This Screencap demonstrates it:

http://img528.imageshack.us/my.php?image=arrayformulasnotarrayenfd5.jpg

So it seem that it was very special and very limited circumstances that
cause me to be mislead. That will teach me to use =ROW() as a lazy input of
sample data!

--
Thank again,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk