# Avg Formula

#### mojo300

##### Active Member
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
Will AVERAGEIF work here?

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

and copy it down

Expand the B range as required

#### prabby25101981

##### Active Member
Maybe this -

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

Change the range accordingly...

#### mojo300

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

#### prabby25101981

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

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

Last edited:

Replies
0
Views
441
Replies
7
Views
429
Replies
1
Views
264
Replies
5
Views
320
Replies
3
Views
251

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.

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

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

### Which adblocker are you using?

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

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