Compa-Ratio (Pay vs Midpoint)

Excel Formulas › HR & Payroll

All versionsROUND

A compa-ratio compares an employee’s pay to the midpoint of their salary range — 1.00 means exactly at midpoint. It’s the core metric for pay equity and range positioning: salary divided by midpoint.

Quick formula: compa-ratio of a salary against its range midpoint:
=salary / midpoint
Above 1.0 is paid above midpoint; below 1.0 is under. Format as a percentage or a 2-decimal ratio.

Functions used (tap for the full reference guide):

ROUND

The example

$66k against a $60k midpoint.

AB
1ItemValue
2Salary66000
3Midpoint60000 → 1.10

The formula

The formula:

=ROUND(B2 / B3, 2) // 1.00 = at midpoint

How it works

How it works:

  1. salary / midpoint gives the compa-ratio — 1.00 is exactly at the range midpoint.
  2. Above 1.0 means paid above midpoint; below 1.0 means under — useful for spotting pay gaps.
  3. A range penetration variant measures position within the band: (salary - min) / (max - min).
  4. Aggregate with AVERAGE across a team or grade to see overall pay positioning.

Compa-ratio vs range penetration: compa-ratio anchors on the midpoint (1.00 = midpoint), while range penetration anchors on the whole band (0% = minimum, 100% = maximum). HR teams often report both — one shows competitiveness, the other shows headroom for raises.

Try it: interactive demo

Live demo

Salary and midpoint.

Compa-ratio ·

Variations

As a percentage

Percent format:

=salary / midpoint

Range penetration

Position in band:

=(salary - min) / (max - min)

Team average

Overall positioning:

=AVERAGE(compa_ratios)

Pitfalls & errors

Midpoint can’t be zero. A missing or zero midpoint gives #DIV/0! — ensure the range table is filled.

Right range. Match each employee to the midpoint of their grade, not a global one.

Compa ≠ penetration. Don’t confuse the midpoint-based ratio with whole-band penetration.

Practice workbook

📊
Download the free Compa-Ratio (Pay vs Midpoint) practice workbook
A compa-ratio sheet with the percentage, range-penetration, and team-average variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a compa-ratio in Excel?
Divide salary by the range midpoint: =ROUND(salary / midpoint, 2). A result of 1.00 means the employee is paid exactly at midpoint.
What's the difference from range penetration?
Compa-ratio anchors on the midpoint; range penetration = (salary - min)/(max - min) measures position across the whole band from 0% to 100%.
How do I see overall pay positioning?
Average the individual compa-ratios with =AVERAGE(range) across a team or grade.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Round a percentage · Weighted average · Tax bracket lookup

Function references: ROUND