Explanation
This formula sits inside a small summary table with percentile values in column F and gender values in G4 and H4. Working from the inside out, the IF function is set up like this:
IF(Table[Gender]=G$4,Table[Score])
Here, each value in the gender column is tested against the value in G4, “Male”.
The result is an array of boolean values like this:
{88;85;77;FALSE;FALSE;FALSE;83;FALSE;FALSE;79}
Only scores associated with males make it into the array, female scores are translated to FALSE. This array goes into the PERCENTILE function with the k value from F5, 90%.
PERCENTILE automatically ignores FALSE values and returns a result of 86.8.
The reference to Gender in G$4 is locked to prevent the row from changing. The reference to k values, $F5 is locked to prevent the column from changing. As a result, the formula can be copied across the range G5:H7.
Explanation
At the core, this formula uses INDEX to create an expanding reference like this:
INDEX([Color],1):[@Color] // expanding range
On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column.
INDEX([Color],1) // first cell in color
This works because, the INDEX function returns a reference to the first cell, not the actual value. On the right side of the colon, we get a reference to the current row of the color column like this:
[@Color] // current row of Color
This is the standard structured reference syntax for “this row”. Joined with the colon, these two references create a range that expands as the formula is copied down the table. So, we swap these references into the SUM function, we have:
SUM(--(B5:B5=[@Color])) // first row
SUM(--(B5:B11=[@Color])) // last row
Each of the expressions above generates an array of TRUE/FALSE values, and the double negative (–) is used to convert these values to 1s and 0s . So, in the last row, we end up with:
SUM({0;0;0;1;0;0;0;0;1;0;1}) // returns 3
The rest of the formula simply concatenates the color from the current row to the count returned by SUM:
=[@Color]&" - "&3
="Gold"&" - "&3
="Gold - 3"
Simple expanding range?
Why not use a simple expanding range like this?
SUM(--($B$5:B5=[@Color]))
For some reason, this kind of mixed reference becomes corrupted in an Excel Table as rows are added. Using INDEX with a structured reference solves the problem.
