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.
--
Regards,

Paul Hyett, Cheltenham

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.
--
HTH,
Barb Reinhardt



"Paul Hyett" wrote:

> 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.
> --
> Regards,
>
> Paul Hyett, Cheltenham
>

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

--
Regards
Roger Govier



"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
> --
> Regards,
>
> Paul Hyett, Cheltenham

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

"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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.
--
Regards,

Paul Hyett, Cheltenham

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

"RagDyeR" <(E-Mail Removed)> wrote in message
news:e5%(E-Mail Removed)...
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!
=====================================================

"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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.
--
Regards,

Paul Hyett, Cheltenham

Have you tried

=AVERAGE(A1,A4,A7)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
> --
> Regards,
>
> Paul Hyett, Cheltenham

In message <e5#(E-Mail Removed)>, RagDyeR
<(E-Mail Removed)> writes

>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, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In message <(E-Mail Removed)>, Don Guillett
<(E-Mail Removed)> writes

>Have you tried
>
>=AVERAGE(A1,A4,A7)
>
Yes - but you're only allowed 30 cells, and - sods law - I have 31...
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In message <(E-Mail Removed)>, Don Guillett
> <(E-Mail Removed)> writes
>
>>Have you tried
>>
>>=AVERAGE(A1,A4,A7)
>>
> Yes - but you're only allowed 30 cells, and - sods law - I have 31...
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Try enclosing it in double parenthesis:

>>=AVERAGE((A1,A4........,Z29,AA30,AB31))
--
HTH

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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In message <(E-Mail Removed)>, Don Guillett
> <(E-Mail Removed)> writes
>
>>Have you tried
>>
>>=AVERAGE(A1,A4,A7)
>>
> Yes - but you're only allowed 30 cells, and - sods law - I have 31...
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
>