Avg Formula

mojo300

Active Member
Joined
Apr 18, 2006
Messages
301
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
ABCDEF
1AK1995AK1994.571
2AK1994AL1996.741
3AK1995
4AK1994
5AK1995
6AK1994
7AK1995
8AK1994
9AK1995
10AK1994
11AK1995
12AK1994
13AK1995
14AK1995
15AL1991
16AL1996
17AL1997
18AL1997
19AL1997
20AL1999
21AL1999
22AL1999
23AL1999
24AL2007
25AL1991
26AL1991
27AL1991
28AL1991
29AL1986
30AL1991
31AL1996
32AL1997
33AL1997
34AL1997
35AL1999
36AL1999
37AL1999
38AL1999
39AL2007
40AL1986
41AL1991
42AL1996
43AL1997
44AL1997
45AL1997
46AL1998
47AL1999
48AL1999
49AL1999
50AL2007
51AL1986
52AL1991
53AL1996
54AL1997
55AL1997
56AL1997
57AL1998
58AL1999
59AL1999
60AL1999
61AL2007
62AL1986
63AL1991
64AL1996
65AL1997
66AL1997
67AL1997
68AL1999
69AL1999
70AL1999
71AL1999
72AL2007
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Will AVERAGEIF work here?

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

and copy it down

Expand the B range as required
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
Sorry, my formula won't work like that... try this -

=SUMPRODUCT(--(A1:A100=E1),B1:B100)/COUNTIF(A1:A100,E1)
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,742
Messages
5,833,434
Members
430,209
Latest member
addms

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top