I would like to know the formula for calculting average from more than 30 worksheets
Example:
=AVERAGE('1'!N5,'2'!N5,'3'!N5,'4'!N5,'5'!N5,'6'!N5,'7'!N5,'8'!N5,'9'!N5,'10'!N5,'11'!N5,'12'!N5,'13'!N5,'14'!N5,'15'!N5,'16'!N5,'17'!N5,'18'!N5,'19'!N5,'20'!N5,'21'!N5,'22'!N5,'23'!N5,'24'!N5,'25'!N5,'26'!N5,'27'!N5,'28'!N5,'29'!N5,'30'!N5,'31'!N5)
When I give the above formula, it is giving an error message like "more arguements"
M. Raj Kumar
~SB wrote:
Average more than 30 numeric arguments
17-Aug-09
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 am trying to do:
=average(a1,a3,a5,a7,a9,a11,...,a61,a63,a65,...)
I would appreciate any insights.
--
~SB
Previous Posts In This Thread:
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 am 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,
If you are 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!*
:
Re: Average more than 30 numeric arguments
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
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. 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
NiekUsing =AVERAGE((cell1,cell2,cell3............
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
Re: Average more than 30 numeric arguments
Good point!
--ron
Using that "trick" you showed me Gord (<Shift> <F8>), I got 236 cells to work
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
Thanks RDProbably got to the formula 1024 character limit.
Thanks RD
Probably got to the formula 1024 character limit.
Gord
Average of more than 30 numbers
I need help on how to average more than 30 numbers in a in more than 30 worksheets.
You've quoted a previous thread.
You've quoted a previous thread. Which of those suggestions did you try,
and in what way did not it work?
--
David Biddulph
<Raj kumar Moguram> wrote in message
Group them like so=AVERAGE((A1,A3,A7),(A9,A11),...
Group them like so
=AVERAGE((A1,A3,A7),(A9,A11),...,(A61,A63,A65))
HTH
Bob
<Raj kumar Moguram> wrote in message
Insert an empty sheet before the first one that you wish to work with; giveit
Insert an empty sheet before the first one that you wish to work with; give
it the name Start
Insert an empty sheet after the last one that you wish to work with; give it
the name End
To average all the cells C6 on the sheets use =AVERAGE(Start:End!C6)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
in message
If you mean numbers in the same position on different 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.
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 goto>special
(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!)
Submitted via EggHeadCafe - Software Developer Portal of Choice
Server Side Processing in ADO.NET/WCF Data Services
http://www.eggheadcafe.com/tutorial...f-4f6f92a76585/server-side-processing-in.aspx