Average function for more than 30 items

[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

 

[Mangesh Yadav]

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

Use:
=AVERAGE(A1:A100)


Mangesh

 

[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.

 

[Max]

Assuming the tables are identically located within A13 in 32 sheets named
as: Form1, Form2 ... Form32, and Form1 is the first sheet (leftmost), Form32
is the last (rightmost) sheet, we could try this in a new sheet inserted to
the left of Form1

Put the headers into A1:A3:
Factor1
Factor2
Factor3

Put in B1: =AVERAGE(Form1:Form32!B1)
Copy B1 across to D1, fill down to D3

 

[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

 

[Max]

Oops, you had 34 Forms, not 32,
so "32" should have read as: "34" everywhere

 

[Murtaza]

Great help Max.....this newsgroup never disappoints me

 

[Max]

You're welcome !
Thanks for the feedback ..