Statistical
Salary benchmarking formulas: Compa-Ratio, Range Penetration, and Percentile Rank
Compare salary with a defined band midpoint and range, and calculate a rank within the example peer set.
Three distinct HR compensation metrics every analyst needs
Compa-Ratio = Salary / BandMidpoint centers on 1.00 (100%): 0.90 means 10% below market midpoint, while 1.05 means 5% above midpoint.
Range Penetration = (Salary - BandMin) / (BandMax - BandMin) shows how far through the pay grade an employee has progressed (0% at BandMin, 50% at symmetric Midpoint, 100% at BandMax). Peer Percentile Rank = COUNTIF(Range, "<" & Salary) / (COUNT(Range) - 1) matches the unrounded inclusive rank for distinct observed values in this example.
| Employee | Salary (B) | Band $80k–$100k ($90k Mid) | Compa-Ratio | Range Penetration |
|---|---|---|---|---|
| P1 | 78000 | Below Min | 0.8667 | -10.0% |
| P2 | 86000 | Lower Half | 0.9556 | 30.0% |
| P3 | 94500 | Upper Half | 1.0500 (105%) | 72.5% (Peer P50) |
| P4 | 102000 | Above Max | 1.1333 | 110.0% |
| P5 | 110000 | Above Max | 1.2222 | 150.0% |
Compa-Ratio, Range Penetration, and Peer Percentile Rank formulas
The rank demonstration assumes distinct numeric peer salaries and a target already present in that set. It does not implement interpolation for other targets.
=ROUND(B4 / D4, 4)Band Range Penetration and Peer Percentile Rank
Measures exact position inside [BandMin, BandMax] and exact 0-to-1 peer rank.
=ROUND((B4 - C4) / (E4 - C4), 4)Peer rank for the demonstrated distinct observations
Count lower salaries and divide by n-1.
=ROUND(COUNTIF($B$2:$B$6, "<" & B4) / (COUNT($B$2:$B$6) - 1), 4)Inputs and interpretation
Require a positive midpoint, maximum greater than minimum and at least two numeric peer values. A band midpoint is not necessarily a market benchmark. Ties and targets between observations need an explicit percentile convention. These descriptive metrics alone do not establish pay equity or explain compensation differences.
Try the example
Use the original values or change them to test your understanding.