AVERAGE more than 30 arguments?

[Guest]

I have a spreadsheet where I am trying to average the value for every day of
the week. So I need to average 52 Mondays, etc.

If I understand correctly, you can't use average for more than 30 arguments.
(This seems pretty lame).

How else can I do it?

-Raza

 

[Conrad Carlberg]

Hi Raza,

It's true that Excel functions such as AVERAGE can't take more than 30
arguments.

But -- and it's a big one -- an argument can have many more than 30 values.

So this:

=AVERAGE(A1:A65536)

has only one argument, the range A1:A65536, although it might present 65536
values to the AVERAGE function.

 

[KL]

Hi Raza,

If you enter your formula like:

=AVERAGE(A1,A2,A3,A4,A5,...,A30)

you will effectively run into the 30 arguments limit.

However, the following makes the limit difficult to reach:

=AVERAGE((A1,A2,A3,A4,A5,...,A30))

Here all your 30 different cells are in fact just one argument, so you can
add 29 times that amount of cells/ranges.

And of course if your cells are contiguous you shoul use them as one range:

=AVERAGE(A1:A30)

Regards,
KL

 

[Bernie Deitrick]

Raza,

You can also use a SUMIF() divided by a COUNTIF() - describe your data layout for more help.

HTH,
Bernie
MS Excel MVP

 

[KL]

just a small correction/addition: "so you can add 29 times that amount of
cells/ranges", I should actually have said that the number of cells in each
union (single argument) can be much more, the limit, I believe, is the max
length of a formula which is 1024 characters. For example: you could have:

=AVERAGE((A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1,AA1,AB1,AC1,AD1,AE1,AF1,AG1,A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2,AF2,AG2,A3,B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,N3,O3,P3,Q3,R3,S3,T3,U3,V3,W3,X3,Y3,Z3,AA3,AB3,AC3,AD3,AE3,AF3,AG3,A4,B4,C4,D4,E4,F4,G4,H4,I4,J4,K4,L4,M4,N4,O4,P4,Q4,R4,S4,T4,U4,V4,W4,X4,Y4,Z4,AA4,AB4,AC4,AD4,AE4,AF4,AG4,A5,B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5,N5,O5,P5,Q5,R5,S5,T5,U5,V5,W5,X5,Y5,Z5,AA5,AB5,AC5,AD5,AE5,AF5,AG5,A6,B6,C6,D6,E6,F6,G6,H6,I6,J6,K6,L6,M6,N6,O6,P6,Q6,R6,S6,T6,U6,V6,W6,X6,Y6,Z6,AA6,AB6,AC6,AD6,AE6,AF6,AG6,A7,B7,C7,D7,E7,F7,G7,H7,I7,J7,K7,L7,M7,N7,O7,P7,Q7,R7,S7,T7,U7,V7,W7,X7,Y7,Z7,AA7,AB7,AC7,AD7,AE7,AF7,AG7,A8,B8,C8,D8,E8,F8,G8,H8,I8,J8,K8,L8,M8,N8,O8,P8,Q8,R8,S8,T8,U8,V8,W8,X8,Y8,Z8,AA8,AB8,AC8,AD8,AE8,AF8,AG8,A9,B9,C9,D9,E9,F9,G9,H9,I9,J9,K9,L9,M9,N9,O9,P9,Q9,R9,S9,T9,U9,V9,W9,X9,Y9,Z9,AA9,AB9,AC9,AD9,AE9,AF9,AG9,AH1))

which is 297 cells being averaged

Regards,
KL


when I said