VLOOKUP and average in one formula

[cpisthedbb]

Hi,

I've been hours trying to find the answer for this but whatever I try wont work. I've got a large spreadsheet with lot of names on and a lot are repeated with different scores on. What I basically need is a column that averages the scores of a person. The table is massive but in a similar format to :

[TABLE="width: 500"]
<tbody>[TR]
[TD]A Smith[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A Smith[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]A Jones[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A Smith[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A Johnson[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

What I want is to get an average score for each person in the table.
A Smith =
A Jones =
A Johnson =

I'm sure there's an easy way of doing this but I'm having no luck. I've tried VLookup and average but can't seem to get it to work. Any advice would be appreciated.

 

[PCRIDE]

Use AVERAGEIFS

In a cell next to the name type
=averageifs(score range, name range, name)

So if the name was in A2 and the
Scores are in D:D and a list of names next to the scores in a table in C

=averageifs(A2, C:C, D:D)


DOES THAT MAKE Sense?

 

[cpisthedbb]

Use AVERAGEIFS

In a cell next to the name type
=averageifs(score range, name range, name)

So if the name was in A2 and the
Scores are in D:D and a list of names next to the scores in a table in C

=averageifs(A2, C:C, D:D)


DOES THAT MAKE Sense?

Thanks for the reply. What I'm looking for really is a way of doing it without a separate column as I want to show the data on a separate spreadsheet if possible. I'm not sure if there's a way of doing it but would have thought there maybe a way with a vlookup formula?

 

[Aladin Akyurek]

Try a pivot table.

 

[PCRIDE]

Thanks for the reply. What I'm looking for really is a way of doing it without a separate column as I want to show the data on a separate spreadsheet if possible. I'm not sure if there's a way of doing it but would have thought there maybe a way with a vlookup formula?

Yes it will work. If you have your data on another tab, let’s say in 2 columns , then anywhere else (on another tab) insert the formula, you could make a drop down choice or a list

Very simple