HR & Payroll Analytics
Employee Salary Band Lookup, Compa-Ratio & Pay-Range Penetration Formulas
Join employee grades to compensation band minimums, midpoints, and maximums with XLOOKUP to compute Compa-Ratio, Range Penetration %, and Green-Circle / Red-Circle outlier flags.
Why HR compensation reviews track both Compa-Ratio and Range Penetration
Compa-Ratio divides an employee's base salary by their grade midpoint (Salary / Midpoint), showing whether pay sits at market target (1.00). Range Penetration measures how far through the band's total width the employee has progressed: (Salary - Band_Min) / (Band_Max - Band_Min), ranging from 0.00 at band entry to 1.00 at band ceiling.
Flagging salaries below Band_Min ("Below_Min" / green-circled) or above Band_Max ("Above_Max" / red-circled) isolates merit-cycle adjustments automatically.
| Employee (Grade) | Salary | Band (Min / Mid / Max) | Compa-Ratio | Penetration | Status |
|---|---|---|---|---|---|
| Emp_1 (L3) | $85,000 | $70k / $85k / $100k | 1.0000 | 0.5000 | In_Band |
| Emp_2 (L4) | $125,000 | $95k / $115k / $135k | 1.0870 | 0.7500 | In_Band |
| Emp_3 (L5) | $200,000 | $130k / $160k / $190k | 1.2500 | 1.1667 | Above_Max |
Grade-Linked Compa-Ratio & Range Penetration with LET + XLOOKUP
Look up Band_Min, Band_Mid, and Band_Max by grade level to calculate Compa-Ratio and Range Penetration without helper columns.
=LET(b_min, XLOOKUP(B2, $G$2:$G$4, $H$2:$H$4), b_max, XLOOKUP(B2, $G$2:$G$4, $J$2:$J$4), ROUND((C2 - b_min) / (b_max - b_min), 4))Band Outlier Status (Below_Min / In_Band / Above_Max)
Screen for salaries outside approved compensation boundaries before merit increases are finalized.
=LET(b_min, XLOOKUP(B2, $G$2:$G$4, $H$2:$H$4), b_max, XLOOKUP(B2, $G$2:$G$4, $J$2:$J$4), IF(C2 < b_min, "Below_Min", IF(C2 > b_max, "Above_Max", "In_Band")))Try the example
Use the original values or change them to test your understanding.