Average more than 30 numeric arguments

I need help on how to average more than 30 numbers in a single column when
the numbers are not in contiguous rows. Excel tells me I can only have 1 to
30 numeric arguments when I try to average more than 30 cells.
An example of what I'm trying to do:
=average(a1,a3,a5,a7,a9,a11,...,a61,a63,a65,...)

I would appreciate any insights.
--
~SB

If you're always picking an odd row, the trick is to use a contiguous range,
but combine it with an IF statement to "cancel" the un-needed values.

=AVERAGE(IF(MOD(ROW(A1:A65),2)=1,A1:A65))

Input this as an array* formula. Adjust ranges as needed.

* (Use Ctrl+Shift+Enter to confirm, not just Enter)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"~SB" wrote:

I need help on how to average more than 30 numbers in a single column when
the numbers are not in contiguous rows. Excel tells me I can only have 1 to
30 numeric arguments when I try to average more than 30 cells.
An example of what I'm trying to do:
=average(a1,a3,a5,a7,a9,a11,...,a61,a63,a65,...)

I would appreciate any insights.
--
~SB

On Mon, 17 Aug 2009 04:46:01 -0700, ~SB wrote:

I need help on how to average more than 30 numbers in a single column when
the numbers are not in contiguous rows. Excel tells me I can only have 1 to
30 numeric arguments when I try to average more than 30 cells.
An example of what I'm trying to do:
=average(a1,a3,a5,a7,a9,a11,...,a61,a63,a65,... )

I would appreciate any insights.

Another solution than Luke's, if your ranges cannot be related by some formula,
would be to add all the values, then divide by the appropriate number.

For example:

=(SUM(a1,a3,a5,a7) + a11 + a92 +a94 +sum(a100,a200,a300)) / 10

or something similar.
--ron

And if you just put an extra pair of brackets around the list of cells, it
is considered one argument. I don't know how many cells you can put in a
list like this, but certainly more than 30. And of course then you can start
a new list, which will only be the second argument, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ron Rosenfeld" wrote in message
...
On Mon, 17 Aug 2009 04:46:01 -0700, ~SB
wrote:

I need help on how to average more than 30 numbers in a single column when
the numbers are not in contiguous rows. Excel tells me I can only have 1
to
30 numeric arguments when I try to average more than 30 cells.
An example of what I'm trying to do:
=average(a1,a3,a5,a7,a9,a11,...,a61,a63,a65,.. .)

I would appreciate any insights.

Another solution than Luke's, if your ranges cannot be related by some
formula,
would be to add all the values, then divide by the appropriate number.

For example:

=(SUM(a1,a3,a5,a7) + a11 + a92 +a94 +sum(a100,a200,a300)) / 10

or something similar.
--ron

Niek

Using =AVERAGE((cell1,cell2,cell3............))

I quit adding non-contiguous cells at 100 and no problem to that point.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 15:07:10 +0200, "Niek Otten" wrote:

And if you just put an extra pair of brackets around the list of cells, it
is considered one argument. I don't know how many cells you can put in a
list like this, but certainly more than 30. And of course then you can start
a new list, which will only be the second argument, etc

On Mon, 17 Aug 2009 15:07:10 +0200, "Niek Otten" wrote:

And if you just put an extra pair of brackets around the list of cells, it
is considered one argument. I don't know how many cells you can put in a
list like this, but certainly more than 30. And of course then you can start
a new list, which will only be the second argument, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Good point!
--ron

Using that "trick" you showed me Gord (Shift F8), I got 236 cells to
work before the "Formula Too Long" error popped up.

BTW, it (Shift F8) also works well for selecting non-contiguous cells
for the "Named Range" data entry procedure.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gord Dibben" gorddibbATshawDOTca wrote in message
...
Niek

Using =AVERAGE((cell1,cell2,cell3............))

I quit adding non-contiguous cells at 100 and no problem to that point.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 15:07:10 +0200, "Niek Otten"
wrote:

And if you just put an extra pair of brackets around the list of cells, it
is considered one argument. I don't know how many cells you can put in a
list like this, but certainly more than 30. And of course then you can
start
a new list, which will only be the second argument, etc

Thanks RD

Probably got to the formula 1024 character limit.


Gord


On Mon, 17 Aug 2009 12:10:31 -0700, "RagDyer" wrote:

Using that "trick" you showed me Gord (Shift F8), I got 236 cells to
work before the "Formula Too Long" error popped up.

BTW, it (Shift F8) also works well for selecting non-contiguous cells
for the "Named Range" data entry procedure.

I need help on how to average more than 30 numbers in more than 30
worksheets.

If you mean numbers in the same position on different worksheets, try:
=AVERAGE(Sheet1:Sheet31!E3)

If the cells differ from sheet to sheet, you have to enter each
individually:
=Sheet1!A1 + Sheet2!B2 + Sheet3!C3 + ... + Sheet31!AE31

Excel 2007 allows up to 255 values in AVERAGE and other functions.


Niek Otten wrote:

And if you just put an extra pair of brackets around the list of cells, it
is considered one argument. I do not know how many cells you can put in a
list like this, but certainly more than 30...

I think you can include up to 32,768 areas in function arguments, so the
formula length limitation always comes first. This has been extended in
Excel 2010.

You can test this by entering =sum((s,s,s,s)) where s:=selection() is a
defined name. First select 8192 values in alternate rows using gotospecial
(the maximum allowed up to Excel 2007), press Ctrl+Alt+F9 to recalculate.
Now try replacing the formula with =sum((s,s,s,s,a1)), select rows and
recalculate and it runs out of memory. (Ignore circular references and make
sure to save first!)