Average function for more than 30 items

M

Murtaza

How can I use Average function to calculate average of more than 30 items?
becasue Average function do not accept 31st item & gives an error

Or Is there any other alternative.

Thank you,
Murtaza
 
M

Mangesh Yadav

Don't use
=AVERAGE(A1,A2,A3,A4,...,A100) ... won't work

Use:
=AVERAGE(A1:A100)


Mangesh
 
M

Murtaza

Dear Mangesh
The values are not sequenced in a list, therefore I couldn't use the
"A1:A100" refrencing.

I try to explain you more in the following example:

Form I
Factor1 4 5 6
Factor2 7 5 6
Factor3 8 7 5

Form II
Factor1 5 7 2
Factor2 9 2 7
Factor3 3 7 4

and other 32 Forms with same factors...............then I need to get the
automated Average of above 34 Forms

Averages
Factor1 4.50 6.00 4.00

Factor2 8.00 3.50 6.50

Factor3 5.50 7.00 4.50


Hope, I made myself more clear.
 
R

Roger Govier

Hi

Current versions of Excel will not allow more than 30 items to be used
in this way. Excel 12 increases this to 255.

However, even though your data is not in a contiguous block, you could use
=SUMIF($A$1:$A$100,"Factor1",B$1:B$1000)/COUNTIF($A$1:$A$1000,"Factor1")

If you have your summary at the bottom with your Factor1, Factor2,
factor3 in cells say A1001:A1003, then you can replace "Factor1" in the
formula with the cell reference then just copy across through columns C
and D to give your other Forms

=SUMIF($A$1:$A$100,$A$1001,B$1:B$1000)/COUNTIF($A$1:$A$1000,$A$1001)

Change ranges to suit

Regards

Roger Govier
 
Top