Return a value from a table that is in the top n percent of a person's trial scores

[PD in Waterloo]

I have an Excel 365 workbook with a data table on one tab and a results page on another tab. The data table contains a column with the results of hundreds of trials, each with a numerical trial score earned by the participant whose corresponding name is in the adjacent column. Each name is repeated hundreds of times. The data are currently sorted:

1. Participant names by alpha

2. Scores hi-low

There are other columns (date, trial_type, etc), immaterial for this project.

I want to craft a formula that returns the average of the top top n percent of trial scores for each person.

By way of example, if I just needed the average of each person's scores, I would use:

AVERAGEIF(DATASET[name]:[name]],$A4,DATASET[TRIAL_SCORE]) // $a4 is the participant's name

I was able to figure out a formula to capture the average of the top n count of each person's scores:

=AVERAGEIFS(DATASET[[trial_score]:[trial_score]],DATASET[[name]:[name]],
$A4,DATASET[[trial_score]:[trial_score]],">="&LARGE(IF(DATASET[[name]:[name]]=$A4,
DATASET[[trial_score]:[trial_score]]),J$3)) // $A4 is the relative cell ref for the participant’s name; j$3 is the relative cell ref atop several columns with the desired count parameter

But I just can't suss out a formula to get the averages of only a subset each participant's scores, namely the top *n* percent of his or her scores (with the *n* value in a reference cell).

I thought about adding a column to the table to generate a "1" of "0" result based on whether the score in that row was in the top n percent of the person's scores, and then adding the "1" to the conditions of an AVERAGEIFS formula.

Seems kludgy in concept so any more elegant suggestions/ideas gratefully received!

 

[PD in Waterloo]

I did reply. This solution seems to assume that there is some number of undifferentiated scores (74 or 75 in the post), making it easy to calculate n% of that number (7-8), and then insert that result into a reference cell that is then used to extract the average of the top x (count) of scores. It works fine if there's only one person's scores, or if the scores are all commingled. But this data set had almost 900 participants, each with 100-1000+ scores. What I am hoping to do is use a formula to extract the top n% of each person's scores, then average each of those extracted individual averaged scores and report it in a summary.

 

[Fluff]

Yes, but you haven't answered my question. Regardless of how many names you have we need to know if the n% should always be rounded up, rounded down, or round to the nearest whole number.

 

[PD in Waterloo]

OK Just round to one place of decimal.

 

[Fluff]

You cannot round to 1 decimal, it needs to be a whole number.