Calculate percentiles for salary benchmarking
IntermediatePercentiles help you understand how values are distributed across a dataset. In compensation analysis, knowing that the 75th percentile salary is $87,000 tells you that three-quarters of employees earn at or below that amount.
PERCENTILE.INC returns the value at a given percentile within a range. The "INC" means the calculation includes both 0 and 1 as possible values (the minimum and maximum of the dataset). The function takes two arguments:
=PERCENTILE.INC(array, k)
- array: the range of values to analyze
- k: the percentile as a decimal between 0 and 1 (0.25 for the 25th percentile, 0.5 for the 50th, and so on)
For example, =PERCENTILE.INC(A1:A10, 0.5) returns the median of the values in A1:A10.
Your task
The spreadsheet shows annual salaries for 12 employees in rows 2 through 13 (column C). Below the table is a summary section where you'll calculate four percentile benchmarks.
- B15: 25th percentile
- B16: 50th percentile (the median)
- B17: 75th percentile
- B18: 90th percentile
Enter a PERCENTILE.INC formula in each cell. Every formula uses the same salary range; only the k value changes for each percentile.
When you're done, review the results to see how the salaries spread out from the 25th percentile through the 90th percentile.
Need some help?
Hint 1
k represents the percentile as a decimal. For the 25th percentile use 0.25, for the 50th use 0.5, for the 75th use 0.75, and for the 90th use 0.9.
Hint 2
All four formulas reference the same salary range. Using an absolute reference (with the $ signs) in the array argument lets you write the formula once and copy it to the other rows.
Related function(s)
Calculate percentiles for salary benchmarking
IntermediatePercentiles help you understand how values are distributed across a dataset. In compensation analysis, knowing that the 75th percentile salary is $87,000 tells you that three-quarters of employees earn at or below that amount.
PERCENTILE.INC returns the value at a given percentile within a range. The "INC" means the calculation includes both 0 and 1 as possible values (the minimum and maximum of the dataset). The function takes two arguments:
=PERCENTILE.INC(array, k)
- array: the range of values to analyze
- k: the percentile as a decimal between 0 and 1 (0.25 for the 25th percentile, 0.5 for the 50th, and so on)
For example, =PERCENTILE.INC(A1:A10, 0.5) returns the median of the values in A1:A10.
Your task
The spreadsheet shows annual salaries for 12 employees in rows 2 through 13 (column C). Below the table is a summary section where you'll calculate four percentile benchmarks.
- B15: 25th percentile
- B16: 50th percentile (the median)
- B17: 75th percentile
- B18: 90th percentile
Enter a PERCENTILE.INC formula in each cell. Every formula uses the same salary range; only the k value changes for each percentile.
When you're done, review the results to see how the salaries spread out from the 25th percentile through the 90th percentile.
Need some help?
Hint 1
k represents the percentile as a decimal. For the 25th percentile use 0.25, for the 50th use 0.5, for the 75th use 0.75, and for the 90th use 0.9.
Hint 2
All four formulas reference the same salary range. Using an absolute reference (with the $ signs) in the array argument lets you write the formula once and copy it to the other rows.