Averaging non-continuous ranges

[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.

 

[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.

 

[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...)

 

[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.

 

[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.

 

[Don Guillett]

Have you tried

=AVERAGE(A1,A4,A7)

 

[Paul Hyett]

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

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.

 

[Paul Hyett]

Have you tried

=AVERAGE(A1,A4,A7)

Yes - but you're only allowed 30 cells, and - sods law - I have 31...

 

[Don Guillett]

try this idea
=AVERAGE(AVERAGE(A1,A4,A7),AVERAGE(A10,A12))

 

[Sandy Mann]

Try enclosing it in double parenthesis:
--
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

 

[Don Guillett]

Nice
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Try enclosing it in double parenthesis:
--
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

 

[Sandy Mann]

Nice

Ragdyer's nice and he said he saw it when an OP posted back and said he
found the answer himself.

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

Nice

 

[Don Guillett]

I wonder why that post did not appear for ME??

 

[Sandy Mann]

http://groups.google.co.uk/groups?a...81&as_maxd=12&as_maxm=9&as_maxy=2007&safe=off

Although I had difficulty in finding it in Google. I hope that they get it
sorted out soon.

--
Regards,

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

 

[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?

 

[Paul Hyett]

In microsoft.public.excel on Wed, 12 Sep 2007, Sandy Mann

Try enclosing it in double parenthesis:

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

Thank you.

 

[joeu2004]

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

Look at the union reference operator on the "About calculation
operators" Help page.

 

[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

 

[Don Guillett]

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

 

[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

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