30 Arugments limit- SUM, AVERAGE, etc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to work around the limit of 30 arguments limitation for common
functions?

I'm trying to average a large number of ranges on seperate worksheets. Is
this possible?

Thank you!
 
It sounds possible. Can you give (part of) an example of how you would do it
if you did it with the method that would run up against the limit? I.e.,
write out a few parts of it so people can see what you're trying to do.

Are the ranges the same on each worksheet?

"Bryan - (e-mail address removed)" <Bryan -
(e-mail address removed)@discussions.microsoft.com> wrote in message
news:[email protected]...
 
Dave R. wrote...
It sounds possible. Can you give (part of) an example of how you would do it
if you did it with the method that would run up against the limit? I.e.,
write out a few parts of it so people can see what you're trying to do.

Are the ranges the same on each worksheet?
Click to expand...
....

Ranges on the same worksheet can be grouped.

=AVERAGE(A1:B10,D11:E20,G21:H30)

has 3 arguments, but

=AVERAGE((A1:B10,D11:E20,G21:H30))

has 1 argument, a multiple-area range.

Dave's question, if you answer affirmatively, would lead to using 3D
references like

=AVERAGE(Sheet1:Sheet100!C5:J10)

When all else fails, use brute force.

=SUM(SUM(Ref1,Ref2,...),SUM(Ref31,Ref32,...),...,SUM(Refx,Refy,...))
/SUM(COUNT(Ref1,Ref2,...),COUNT(Ref31,Ref32,...),...,COUNT(Refx,Refy,...))

You'll run out of available characters in individual cell formulas
before you exhaust this approach.
 
Thanks so much!

Harlan Grove said:
Dave R. wrote...
....

Ranges on the same worksheet can be grouped.

=AVERAGE(A1:B10,D11:E20,G21:H30)

has 3 arguments, but

=AVERAGE((A1:B10,D11:E20,G21:H30))

has 1 argument, a multiple-area range.

Dave's question, if you answer affirmatively, would lead to using 3D
references like

=AVERAGE(Sheet1:Sheet100!C5:J10)

When all else fails, use brute force.

=SUM(SUM(Ref1,Ref2,...),SUM(Ref31,Ref32,...),...,SUM(Refx,Refy,...))
/SUM(COUNT(Ref1,Ref2,...),COUNT(Ref31,Ref32,...),...,COUNT(Refx,Refy,...))

You'll run out of available characters in individual cell formulas
before you exhaust this approach.
Click to expand...
 
Back
Top