Help combining the Average If/Percentile Function

Here is my shot at it.

First we need to calculate the 85th percentile for a player. This is done by what used to be an array formula: =PERCENTILE(IF([Player]=[@Player],[PTS],FALSE),0.85). The IF statement says only consider rows that match the player name. You wind up with the same number for each row that has that player's name. This is the player's 85th percentile.

Then I compare the score with the percentile to get a True / False. Then use AVERAGEIFS.

Oh yeah, I forgot to mention: I converted your data range into an Excel Table. Excel Tables have too many advantages not to use them: they "know" how big they are so if you add or subtract data, formulas, pivot tables and charts will adjust automatically along with them. They also "remember" formulas and copy them down automatically even if you delete the data and copy and paste in new data.

As you also noted, formulas can be expressed in terms of the column headers which makes them easier to understand. [Column Name] means the whole column and [@Column Name] or [@[Column Name]] (both work) means the value on that row.

Intelisense helps you fill these names in. So if you type "[" you'll get a list of the column headers in the table. If you select the cell on the same row, you'll get the @ version automatically.

To reference the columns from outside the table, use TableName[ColumnName].

This nomenclature also carries over into VB code.

To create a table from a range, right click any cell in the range and follow the prompts. You may be asked if the table has headers and you will be asked to select the style (color) table you want. Excel does a good job guessing the "borders" of the table. If it doesn't you can change the range manually.

The table name is assigned automatically as Table1, Table2, ... You can change this name. I generally keep the Table_ prefix and give it a descriptive name like Table_Data or Table_Inventory.