4

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:

Al     1
Bob    1
Bob    2
Bob    1
Joe    4
Joe    2
Kevin  1
Kevin  8
Sally  5

For instance, Kevin should be 4.5 not 8 or 1 and only appear on my new list once.

Improve this question
0

3 Answers 3

Reset to default
3

You can do this with a combination of SUMIF and COUNTIF.

Assuming that your example table starts in A1:

=SUMIF(A1:A9,E1, B1:B9)/COUNTIF(A1:A9,E1)

That would give you the average of whatever name is in E1. You will need to type in the name list manually.

Improve this answer
2
  • 1
    I've been using Excel 2007 and only yesterday did I realize there's an AVERAGEIF function now. Amazing.
    – variant
    Aug 31, 2010 at 11:56
  • 1
    I didn't know they had that now, either! Thanks for the heads up.
    – JNK
    Aug 31, 2010 at 12:22
4

You could use a PivotTable. Add both fields to the PivotTable and change the value field setting to "average of" instead of the default "sum of".

Improve this answer
3
  • how does one use a pivot table>?
    – Robert A. Fettikowski
    Aug 30, 2010 at 17:31
  • Select data, then use Insert > PivotTable (assuming Excel 2007, may be different on other versions).
    – NightSicarius
    Aug 30, 2010 at 17:34
  • I think this is a better answer.
    – Sux2Lose
    Aug 30, 2010 at 19:13
2

Use the Subtotal command. First select the data you wish to "subtotal" and then select Data>>Subtotal:

alt text

Select ok, then select the function to be average:

alt text

click ok and then everything is done for you!

alt text

In order to show just the Averages of the user, simply look at the left hand side and click on the "negative" symbols to close the data in between:

alt text

Improve this answer
4
  • This wouldn't accomplish the OP's wish to have each name only once, but I love your method.
    – JNK
    Aug 30, 2010 at 17:36
  • sorry forgot to add the last part.
    – James Mertz
    Aug 30, 2010 at 17:43
  • 1
    Like this method. It leaves the data intact and gives the results how the OP needs them. Never destroy the original data...just move the results somewhere nicer to look at!
    – RocketGoal
    Aug 31, 2010 at 8:57
  • Ya, I'm not sure which version have it, it appears that 2007+ do but not sure if prev do. I'm running 2010
    – James Mertz
    Aug 31, 2010 at 13:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .