Compa-ratio: formula, Excel example and how to read it

The formula

Compa-ratio = salary ÷ midpoint of the salary band

A salary band gives each grade a minimum, a midpoint and a maximum. The midpoint is usually set at the market rate for a fully competent person in the role. A compa-ratio of 1.00 means paid exactly at the midpoint, 0.90 means 10% below it and 1.10 means 10% above. Some companies show it as a percentage (90%, 110%) instead. The calculation is identical, only the number format differs, so pick one style and use it in every report.

Step 1: find each person's midpoint with a lookup

Keep the band table separate from the employee list and look the midpoint up by grade: =XLOOKUP(B7,$A$2:$A$4,$C$2:$C$4), or =VLOOKUP(B7,$A$2:$D$4,3,FALSE) in older Excel versions. When the bands are updated next year, you change three rows of the band table and every compa-ratio updates.

Step 2: the compa-ratio

Divide salary by midpoint: =C7/D7. Ana earns $57,040 in grade G2, whose midpoint is $62,000: a compa-ratio of 0.92. Dev earns $70,400 in G3 with a midpoint of $80,000: 0.88. Elena, also in G3, is at 1.08.

How to read it: people new to a role, or still learning it, usually sit below 1.00. Experienced, strong performers are often above it. A ratio well below the range typical for someone's experience is the signal to look at.

Worked example: compa-ratios for six employees

The band table is at the top. Each employee row looks up its own band, so the formulas can be filled down. All names and salaries are fictional.

Worked example: compa-ratios for six employees

	A	B	C	D	E	F	G
1	Grade	Minimum	Midpoint	Maximum
2	G1	40,000	48,000	56,000
3	G2	52,000	62,000	72,000
4	G3	66,000	80,000	94,000
5
6	Employee	Grade	Salary	Midpoint	Compa-ratio	Range penetration	Flag
7	Ana	G2	57,040	62,000	0.92	25%
8	Ben	G2	64,480	62,000	1.04	62%
9	Chloe	G1	46,560	48,000	0.97	41%
10	Dev	G3	70,400	80,000	0.88	16%	Review
11	Elena	G3	86,400	80,000	1.08	73%
12	Farid	G2	59,520	62,000	0.96	38%
13
14	Group compa-ratio, grade G2				0.973
15	Median salary, grade G2				59,520

Step 3: range penetration

The compa-ratio compares with the midpoint only. Range penetration shows where in the whole band a salary sits: (salary − minimum) ÷ (maximum − minimum). 0% is the band minimum, 100% the maximum. Dev's salary is 16% of the way through the G3 band, close to its bottom, while Elena is at 73%. Range penetration is more useful than the compa-ratio when bands have different widths.

Step 4: flags and group ratios

A simple =IF(E7<0.9,"Review","") flags everyone below 0.90. That threshold is a policy choice, not a rule: some companies use 0.85 or 0.80. A flag is a prompt to check, not a conclusion. There may be a good reason, such as someone who joined last month.

For a whole grade or team, the group compa-ratio is total salaries ÷ total midpoints: =SUMIFS(C7:C12,B7:B12,"G2")/SUMIFS(D7:D12,B7:B12,"G2") gives 0.973 for G2. In other words, the grade as a whole is paid about 3% below the midpoint.

Turning the flags into a review list

In Microsoft 365 you can pull the flagged employees into a separate list automatically: =FILTER(A7:C12,E7:E12<0.9,"None") returns name, grade and salary for everyone below 0.90. =SORTBY(A7:E12,E7:E12,1) sorts the whole table from the lowest compa-ratio upwards, which is a practical order for a pay review meeting. Add each person's time in role next to the ratio, because a new joiner at 0.88 and a five-year employee at 0.88 are very different cases.

Use the median, not only the average

When you compare pay across groups, one very high salary can pull the average up a long way. The median hardly moves. =MEDIAN(IF(B7:B12="G2",C7:C12)) returns the median salary of grade G2 ($59,520). In older Excel versions, confirm this formula with Ctrl+Shift+Enter. With only three people the median and the average ($60,347) are close, but in real data with dozens of people per grade the difference can be large.

Treat pay data with care

Salary data is confidential personal data. Keep working files access-restricted, remove names before sharing analyses, and follow your company's data protection rules. When you present results, show grades or anonymous groups rather than individuals wherever possible.

Common mistakes

• Dividing by the band maximum or minimum instead of the midpoint.
• Averaging individual compa-ratios for a group. Total salary ÷ total midpoint is the standard group measure and weights everyone correctly.
• Comparing part-time salaries with full-time bands. Convert part-time pay to its full-time equivalent first.
• Lookups without FALSE or exact match. An approximate VLOOKUP on grade codes can silently return the wrong band.
• Reading a low ratio as unfair pay by default. Time in role, performance and recent promotions all matter. Use the ratio to choose what to review.