Calculating Average across multiple rows based on a specific match in another column

Hi all,

New to the forum and currently stumped with a problem. Hope someone can figure this out.

Here is what I am trying to do:

Sheet1 contains columns Name, score and others. . .
Sheet2 contains a Data Validation selection list based on unique names in Sheet1, Total Scores and N.

I select a Name on Sheet2 from the Data Validation, Total Number of Scores and N are calculate by some formulas.

What I need to do is Calculate a Average for the N smallest Total Scores of all the matched Names in Sheet1.

For instance:

Sheet1 contains:

Name	UniqueNames	Score
A	A	1
A	B	2
A		2
A		5
A		7
B		3
B		6
B		6
B		6
B		6
B		7

Sheet2 contains:

SelectedName	ScoreCount	N	Average
A	5	3	1.67

The Average for A should be (1+2+2)/3

SelectedName	ScoreCount	N	Average
B	6	4	5.25

The Average for B should be (3+6+6+6)/4

This seems like it should be so easy, but I can't get the RANGE for the SMALL function to select all the rows in Sheet1 that contain the SelectedName.

With the following array formula on Sheet2 for Average I can get the correct average - problem is the Sheet1!C1:C5 is not dynamic based on the SelectedName - I can't figure that part out.

=AVERAGE(SMALL(Sheet1!C1:C5,ROW(INDIRECT(CONCATENATE("1:",C1)))))

Basically I need a formula that would return the range for Column C in Sheet1 that match’s all selectedName from Sheet2 with Name in Sheet1.

I’ve tried variations of VLOOKUP, INDEX, MATCH, etc. and just can’t figure it out.

Hope this makes Sense - any help would be much appreciated.

Thanks,

Rick

Thanks Cheshire,

I didn't even think about duplicates. What if the scores themselves were sorted in descending order within Name, would there be a way to Average the first N rows of Scores where the name matches?

Rick