How to Calculate Compa-Ratio in Excel for Salary Ranges

Compa-ratio compares a salary with the midpoint of its pay range, and in Excel it is one division: salary divided by midpoint. The work is in finding the right midpoint for each person, which XLOOKUP does by pay grade. This guide is for HR, payroll and analyst job seekers who need to build or check a compa-ratio column. All the salaries and ranges below are made-up example figures for practice, not pay benchmarks or advice.

What is compa-ratio?#

Compa-ratio (short for comparative ratio) shows where one salary sits compared with the middle of its pay range:

Compa-ratio = Salary / Range midpoint

• 1.00 (100%) means the salary equals the midpoint.
• Below 1.00 means the salary is below the midpoint. 0.90 is 10% below.
• Above 1.00 means it is above the midpoint. 1.05 is 5% above.

A pay range is the minimum and maximum an organisation sets for a job or grade. The midpoint is halfway between them. Compa-ratio is a ratio like any other, so the ideas in the guide to calculating ratios in Excel apply here too.

The compa ratio Excel formula for one employee#

If the salary and the midpoint are on the same row, divide one by the other. Say C2 holds 52,000 and F2 holds the midpoint, 56,000:

=C2/F2

The result is 0.9285714…, which reads as about 0.93, or 93% of the midpoint. Excel shows long decimals by default, so you will usually want to round or format it (see below).

Always put the salary on top. =F2/C2 gives 1.08, which looks like "above the midpoint" when the salary is really below it.

How to look up the midpoint with XLOOKUP#

In real files, salaries sit in one table and the pay ranges in another, linked by grade. XLOOKUP brings the right midpoint to each row. Here is the example data:

In D2, type:

=C2/XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4)

XLOOKUP finds "G2" in the grade list and returns its midpoint, 56,000. The $ signs lock the grade table so it stays put when you fill down (Windows: Ctrl+D; Mac: ⌘D). The results are:

XLOOKUP needs Microsoft 365 or Excel 2021 or later. In older versions, use =C2/VLOOKUP(B2,$E$2:$F$4,2,FALSE), which returns the same values. The XLOOKUP guide and the XLOOKUP function reference cover the other arguments, such as a message when a grade is missing.

How to calculate the midpoint from a minimum and maximum#

If the range table has only a minimum and a maximum, add a midpoint column first:

=(B2+C2)/2

For a range of 46,000 to 66,000, this gives 56,000. The brackets matter: without them, Excel divides only the maximum by 2. Keep the midpoint in its own column so you can check it and reuse it.

The same table also gives you range penetration, which shows how far through the range a salary sits:

=(Salary-Minimum)/(Maximum-Minimum)

For Ana, that is (52,000 − 46,000) / (66,000 − 46,000) = 0.30, or 30% of the way from the minimum to the maximum. Compa-ratio and range penetration answer different questions, so reports often show both.

How to round or show compa-ratio as a percentage#

You have two good options, and both keep the real number in the cell:

1. Round to two decimals with ROUND: =ROUND(C2/XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4),2) returns 0.93 for Ana. The ROUND function guide explains the second argument.
2. Format as a percentage: select the column and press Ctrl+Shift+% (Mac: ⌃⇧%). 0.9285… shows as 93%. Add a decimal place with the Increase Decimal button if you need 92.9%.

Don't multiply by 100 and type a % sign into the cell. That turns the value into 93, which breaks any later maths. The percentage formula guide covers this trap.

To show how far above or below the midpoint someone is, subtract 1: =D2-1 gives −0.07 for Ana, or −7% when formatted as a percentage. That is the same idea as a percent change from the midpoint.

How to summarise compa-ratios for a team#

Once each row has a compa-ratio, the usual summaries are one formula each:

An average hides the spread, so read it next to the individual values. A team with one person at 0.80 and one at 1.20 averages 1.00. These figures are descriptions of the example data only; how to act on them depends on the organisation's own pay policy.

Common compa-ratio mistakes in Excel#

Watch for these when you build or check the column:

1. Dividing the wrong way round. Salary goes on top: =C2/F2.
2. Unlocked lookup ranges. Without $, the grade table shifts down as you fill and later rows return #N/A.
3. Grades that don't match. "G2" and "G2 " (with a space) are different to Excel. Clean the grade column with TRIM.
4. Mixing pay periods. An hourly rate divided by an annual midpoint gives nonsense. Convert to the same basis first.
5. Rounding too early. Round the final ratio, not the midpoint or the salary.

Compa-ratio is one of the calculations covered in Excel skills for HR jobs. If you also handle pay runs, the payroll calculator guide shows how gross-to-net columns fit together.

Practise it#

Compa-ratio is a lookup plus a percentage, so practise both parts. Practise XLOOKUP in a live spreadsheet to bring the right midpoint to each row, then use the percentages and percent change lesson to format and read ratios with confidence. Each step checks your formula as soon as you press Enter.