I have an Excel 365 workbook with a data table on one tab and a results page on another tab. The data table contains, among other columns, a column with the results of hundreds of trials, each with a numerical score assigned to the adjacent name. Each name is repeated hundreds of times. The data are currently sorted:
- Participant names by alpha
- 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:$A2700 is the range of participants' names
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).
Any ideas gratefully received!
