Confident Cells

Pay scales: practice exercises

Pay points, part-time pay, increments, pay bands, pay rises. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

The pay scale is in G1:H13. In C2, look up each person's salary from their point.

ABCDEFGH
1EmployeePointSalaryPointSalary
2Priya121£23,400.00
3Femi92£23,853.00
4Marek53£24,528.00
5Tom84£25,396.00
6Will125£25,888.00
7Dev86£26,480.00
8Maria67£27,208.00
9Sam28£27,721.00
10Ella29£28,507.00
11Nadia1110£29,113.00
12Hamza911£29,813.00
13Kerry312£30,721.00

How it works

Many organisations pay by points on a pay scale. Look the point up in the scale with XLOOKUP (or VLOOKUP with FALSE) to get the salary.

When a lookup should find the band a value falls into, rather than an exact match, give XLOOKUP a match mode of -1: it finds the exact value, or the next one down. MIN and MAX set limits, such as the top point of a grade or the smallest pay rise.

=XLOOKUP(B3,$F$2:$F$6,$G$2:$G$6) =ROUND(C3*D3/$I$1,2) =MIN(B3+1,5) =XLOOKUP(25000,$G$2:$G$6,$F$2:$F$6,,-1) =MAX(ROUND(24800*1.03,0),24800+1000)

Points on a pay scale

Many pay scales give each point a salary. Look the point up to get the pay: XLOOKUP(point, points, salaries).

Part-time pay is the full-time salary × hours ÷ full-time hours, rounded to the penny.

Worked example

ABCDEFGHI
1NamePointSalaryHoursActualPointSalaryFT hours37
2Amira3371£24,000.00
3Ben522.52£24,800.00
4Cara2303£25,700.00
54£26,600.00
65£27,500.00
  1. =XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6) in C2. Amira is on point 3: £25,700.
  2. =ROUND(XLOOKUP(B3,$F$2:$F$6,$G$2:$G$6)*D3/$I$1,2) in E3. Ben is on point 5 (£27,500) but works 22.5 of 37 hours, so he's paid £16,722.97.
  3. =MAX(ROUND(24800*1.03,0),24800+1000) in E6. A 3% rise or £1,000, whichever is more. 3% of £24,800 is only £744, so the £1,000 wins: £25,800.

Limits and bands

  • Moving up a point each year, but not past the top of the grade: MIN(point+1, top point).
  • To find the band a salary falls into, list the lowest salary in each band and use XLOOKUP with match mode -1: the exact value, or the next one down.

What you'll practise

  1. The pay scale is in G1:H13. In C2, look up each person's salary from their point.
  2. Part-time staff are paid the share of the full-time salary that matches their hours. Full-time hours are in H1. In D2, work out each person's actual salary, rounded to the nearest penny.
  3. Each year, staff move up one point, but not past the top point of their grade (in G1:H5). In D2, work out each person's point for next year.
  4. The pay bands are in G1:H6, with the lowest salary in each band. In C2, look up the band each salary falls into.
  5. This year's pay award is the percentage in H1 or the amount in H2, whichever is more. In C2, work out each person's new salary, rounded to the nearest pound.