The opposite approach to these calculations is to take a value (like a salary) and determine where it ranks as a percentage within a range of other values.
This can be accomplished using the PERCENTRANK functions.
We’ll start with the inclusive version of PERCENTRANK.
PERCENTRANK.INC has the following syntax:
=PERCENTRANK.INC( array, x )
Array is the list of values to be calculated.
X is the value you need to determine the rank for.
For our example, determine the percentile rank for a salary of $65,000.
=PERCENTRANK.INC( B2:B15, 65000 )
Getting Fancy Responses
If you want to get fancy, you could write the following formulas to take the PERCENTRANK.INC results and concatenate them to text with a bit of custom number formatting.
(NOTE: the result for PERCENTRANK.INC is in cell E11.)
=TEXT(E11,"0%") & " of salaries are BELOW your salary"
=TEXT(1-E11,"0%") & " of salaries are ABOVE your salary"
Keep in mind, the above formulas could use cell references for the values (k) or the percentages (x) instead of hard-coded values.