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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,606
Messages
5,512,390
Members
408,889
Latest member
2vbr

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top