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

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

"jgcrews" wrote:

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

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

"jgcrews" wrote:

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

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

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" 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

Nice one Sandy.
Serendipity <g

--

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


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


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

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

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

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

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

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?i...arrayenfd5.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


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


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