Avg Formula

[mojo300]

Is there a quick formula that can do the average for me here I had to do it manually. I have a spreadsheet with thousands of lines and using the average forumla manually is just too long

Excel Workbook
	A	B	C	D	E	F
1	AK	1995			AK	1994.571
2	AK	1994			AL	1996.741
3	AK	1995
4	AK	1994
5	AK	1995
6	AK	1994
7	AK	1995
8	AK	1994
9	AK	1995
10	AK	1994
11	AK	1995
12	AK	1994
13	AK	1995
14	AK	1995
15	AL	1991
16	AL	1996
17	AL	1997
18	AL	1997
19	AL	1997
20	AL	1999
21	AL	1999
22	AL	1999
23	AL	1999
24	AL	2007
25	AL	1991
26	AL	1991
27	AL	1991
28	AL	1991
29	AL	1986
30	AL	1991
31	AL	1996
32	AL	1997
33	AL	1997
34	AL	1997
35	AL	1999
36	AL	1999
37	AL	1999
38	AL	1999
39	AL	2007
40	AL	1986
41	AL	1991
42	AL	1996
43	AL	1997
44	AL	1997
45	AL	1997
46	AL	1998
47	AL	1999
48	AL	1999
49	AL	1999
50	AL	2007
51	AL	1986
52	AL	1991
53	AL	1996
54	AL	1997
55	AL	1997
56	AL	1997
57	AL	1998
58	AL	1999
59	AL	1999
60	AL	1999
61	AL	2007
62	AL	1986
63	AL	1991
64	AL	1996
65	AL	1997
66	AL	1997
67	AL	1997
68	AL	1999
69	AL	1999
70	AL	1999
71	AL	1999
72	AL	2007
Sheet1

 

[Special-K99]

Will AVERAGEIF work here?

=AVERAGEIF(B$1:B$72,"="&E1)

and copy it down

Expand the B range as required

 

[prabby25101981]

Maybe this -

=SUMPRODUCT(--(A1:A100=E1),AVERAGE(B1:B100))

Change the range accordingly...

 

[mojo300]

Thank you for the help but I cannot get either of these to work

 

[prabby25101981]

Sorry, my formula won't work like that... try this -

=SUMPRODUCT(--(A1:A100=E1),B1:B100)/COUNTIF(A1:A100,E1)