Where “Table” is an Excel Table with data in B5:D14. Note: This is an array formula and must be entered with control + shift + enter. Working from the inside out, the IF function is set up like this: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
