Salary Compression: Using PERCENTILE to find outlier errors in compensation structures.

Salary compression occurs when the pay difference between experienced personnel and newer hires shrinks inappropriately, or when pay grades overlap so heavily that subordinate staff earn near or above their managers. When compensation teams use Excel’s PERCENTILE functions to audit pay bands, hidden data errors—such as unadjusted part-time wages, contractor day rates, or executive outliers—distort the distribution and lead to broken formulas like #NUM!.

Quick Answer

To establish clean compensation benchmarks that resist outlier distortion:

1. Use PERCENTILE.INC: Calculate the 25th, 50th (median), and 75th percentiles of annualized salaries per job title:=PERCENTILE.INC(FILTER(Salaries, JobCode = "ENG-02"), 0.50)
2. Catch #NUM! Errors: Ensure the k percentile argument is strictly between 0 and 1, and confirm the filtered range contains at least one numeric record.

---

Identifying Salary Compression

Compression is evaluated using three core metrics within an organization’s pay grades:

[25th Percentile (P25)] <---- [50th Percentile / Median (P50)] ----> [75th Percentile (P75)]
        |                                                                    |
        +----------------------- Interquartile Spread -----------------------+

1. Compa-Ratio: An employee’s salary divided by the midpoint of their assigned pay band. $\text{Compa-Ratio} = \frac{\text{Base Salary}}{\text{Grade Midpoint (P50)}}$
2. Grade Overlap: How far the top tier of a lower job grade extends into the adjacent higher grade.
3. Compression Ratio: The median pay of an entry-level cohort compared against tenured staff in the same grade:A ratio approaching or exceeding 1.00 signals severe compression. $\text{Compression Ratio} = \frac{\text{P50 (New Hires < 1 Year)}}{\text{P50 (Tenured Staff > 5 Years)}}$

---

Why Percentile Formulas Break in Compensation Models

1. The Empty Array #NUM! Error

If you use modern dynamic arrays with FILTER to isolate salary cohorts:

=PERCENTILE.INC(FILTER(Table1[Salary], Table1[JobGrade] = "L4"), 0.5)

If "L4" has no active employees in the dataset, FILTER returns an empty array, causing PERCENTILE.INC to fail with #NUM!.

The Fix: Provide a fallback using the optional argument in FILTER and wrap with error protection:

=IFERROR(PERCENTILE.INC(FILTER(Table1[Salary], Table1[JobGrade] = "L4"), 0.5), "No Cohort Data")

2. PERCENTILE.INC vs. PERCENTILE.EXC

• PERCENTILE.INC (Inclusive): Accepts percentile values k from 0 to 1 (inclusive). It interpolates cleanly across any dataset with at least two values.
• PERCENTILE.EXC (Exclusive): Excludes the extremes 0 and 1. It requires the percentile rank k to satisfy:If you have a small cohort (e.g., three employees in a niche department, where $N = 3$), attempting to evaluate PERCENTILE.EXC(Range, 0.10) throws #NUM! because the requested rank falls outside the mathematically allowed boundaries for that sample size. In compensation structures, always prefer PERCENTILE.INC for stability across small teams. $\frac{1}{N + 1} < k < \frac{N}{N + 1}$

For more details on formula breaks caused by out-of-bounds parameters, see #NUM! in LARGE/SMALL: “k” argument exceeding array size.

---

Step-by-Step Diagnostic: Cleaning Data Before Percentile Auditing

Before calculating percentiles, run this checklist on your base compensation data:

Step 1: Normalize to Full-Time Equivalency (FTE)

Mixing part-time employee wages with full-time annual salaries skews the lower quartile (P25), masking true compression. Create a calculated column for Annualized Base Salary:

=IF(Table1[@FTE] > 0, Table1[@BaseSalary] / Table1[@FTE], Table1[@BaseSalary])

Step 2: Remove Extreme Outliers via IQR Filtering

If your salary band includes an executive or an unadjusted bonus figure, the percentiles will be distorted. To filter outliers mathematically:

1. Calculate the 25th percentile (Q1): =PERCENTILE.INC(Salaries, 0.25)
2. Calculate the 75th percentile (Q3): =PERCENTILE.INC(Salaries, 0.75)
3. Determine the Interquartile Range (IQR): =Q3 - Q1
4. Flag values lying outside the lower bound (Q1 - 1.5 * IQR) or upper bound (Q3 + 1.5 * IQR).

For a full breakdown of building automated statistical outlier fences in Excel, see Outlier Detection: Formula breaks in “Interquartile Range” (IQR) calculations..

---

Practical Compression Alert Formula

To automatically flag an employee whose pay indicates compression relative to their job band, compare their individual compa-ratio against their tenure tier:

=IF(AND(TenureYears >= 5, CompaRatio < 0.85), "Compressed (Tenured Underpaid)", IF(AND(TenureYears <= 1, CompaRatio >= 1.05), "High-Risk Entry Rate", "Normal"))

If your compensation bands use complex step matrices or tiered multipliers, ensure your lookup logic does not exceed nesting limits by referencing Bonus Multipliers: Handling “Nested IF” errors when there are more than 7 commission tiers..