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!

 

[Fluff]

How about

=AVERAGE(TAKE(FILTER(DATASET[trial_score],DATASET[Name]=A4),B4))

Where B4 contains the number of records.

 

[PD in Waterloo]

How about

=AVERAGE(TAKE(FILTER(DATASET[trial_score],DATASET[Name]=A4),B4))

Where B4 contains the number of records.

Thanks for this! A couple of terms that are new to me and I will look them up. I'm not sure I see how this formula extracts values from (DATASET[trial_score]) by first identifying the nth percentile of the values assigned to the [Name]. Do I have to put a separate formula in B4 that calculates that value?

 

[Fluff]

You can just put the number of scores you want to average in B4

 

[PD in Waterloo]

You can just put the number of scores you want to average in B4

Thank you again for staying with this! I have already sussed out a way to average a straight count of the top n values; what I'm having trouble with is getting the average of the top n percent of values. I'll keep plugging. Thanks again!

 

[Fluff]

What should happen if you want the top 10% & there are 74 scores, should it be the top 7 or the top 8.
The same also goes with 10% of 75 scores.

 

[PD in Waterloo]

Right, I see. But the other 200-ish names in the data set all have scores, some 700+, some barely 100. Putting a fixed value into B4 won't work when I copy the formula for the other 200-ish names in the dataset

 

[Fluff]

You have not answered my question.

 

[PD in Waterloo]

Sorry but I don't see a question in the thread.

 

[dreid1011]

Sorry but I don't see a question in the thread.

Fluff's question:

What should happen if you want the top 10% & there are 74 scores, should it be the top 7 or the top 8.
The same also goes with 10% of 75 scores.