Averaging non-continuous ranges

P

Paul Hyett

Hi,

I want to average a number of values that don't fall in one block, e.g.

A1
A4
A7
etc.

The problem being that there are more than 30 of them, and the Average
function is limited to 30 individual cells (or ranges) for my version of
Excel.

Is there any way I can work around this?

The cells will have a constant offset from each other, if that helps.
 
B

Barb Reinhardt

There are two ways I can think of

1) Create a named range of the non-continuous range. In your formula put
=AVERAGE(myNamedRange) and replace with your range
2) Create a User Defined Function to calculate the average of the entered
range. This one is much more complicated than (1) above.
 
R

Roger Govier

Hi Paul

One way
Highlight the cells by holding down control as you click each one, and give
it a name e.g. MyRng by typing that into the Name box (left of column A and
above Row 1)

=Average(MyRng)

If there are too many cells to fit to one named range, create several and
use
=Average(MyRng1+MyRng2 etc...)
 
R

RagDyeR

Try this which I sized for 40 cells:

=SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*(A1:A118<>""))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,

I want to average a number of values that don't fall in one block, e.g.

A1
A4
A7
etc.

The problem being that there are more than 30 of them, and the Average
function is limited to 30 individual cells (or ranges) for my version of
Excel.

Is there any way I can work around this?

The cells will have a constant offset from each other, if that helps.
 
R

RagDyeR

BTW,

If, in the future, your cells do *not* have a constant offset, you can still
reference *more* then 30 cells by simply enclosing the references in
*DOUBLE* parens.

=Average((1,2,3, ... 100,101, ...etc.))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Try this which I sized for 40 cells:

=SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*(A1:A118<>""))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,

I want to average a number of values that don't fall in one block, e.g.

A1
A4
A7
etc.

The problem being that there are more than 30 of them, and the Average
function is limited to 30 individual cells (or ranges) for my version of
Excel.

Is there any way I can work around this?

The cells will have a constant offset from each other, if that helps.
 
P

Paul Hyett

RagDyeR said:
Try this which I sized for 40 cells:

=SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A1
=18)+2,3)=0)*(A1:A118<>""))
Click to expand...
This looks interesting, although I'll have to read up on SUMPRODUCT &
MOD, as I like to understand formulas I'm using if I can.

Thanks for your help, and everyone else's too.
 
S

Sandy Mann

Try enclosing it in double parenthesis:
--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Ragdyer

Yea, but that's the one from *yesterday*!

Can't you guys see my 2nd post in *this* thread ... same thing, only
pertaining to Average(()), almost 16 hours ago?

Is something wrong with the MS servers again?
 
P

Paul Hyett

In microsoft.public.excel on Wed, 12 Sep 2007, Sandy Mann
Try enclosing it in double parenthesis:
Click to expand...

It worked - I'm not sure *why* it worked, but who cares! :)

Thank you.
 
B

Bernd P

Hello,

That's only mathematical correct if ALL of your "sub"AVERAGEs have the
same number of arguments.

For primes like 31 it won't be possible...

Regards,
Bernd
 
D

Don Guillett

A bit more explanation would be helpful. I did test with the formula
presented. However the (( )) is better.
 
S

Sandy Mann

That's another post in this thread that I can't see, I don't see Bernd P's
post at all.

Damned Outlook Express!

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top