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.
The pay scale is in G1:H13. In C2, look up each person's salary from their point.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Point | Salary | Point | Salary | |||
| 2 | Priya | 12 | 1 | £23,400.00 | ||||
| 3 | Femi | 9 | 2 | £23,853.00 | ||||
| 4 | Marek | 5 | 3 | £24,528.00 | ||||
| 5 | Tom | 8 | 4 | £25,396.00 | ||||
| 6 | Will | 12 | 5 | £25,888.00 | ||||
| 7 | Dev | 8 | 6 | £26,480.00 | ||||
| 8 | Maria | 6 | 7 | £27,208.00 | ||||
| 9 | Sam | 2 | 8 | £27,721.00 | ||||
| 10 | Ella | 2 | 9 | £28,507.00 | ||||
| 11 | Nadia | 11 | 10 | £29,113.00 | ||||
| 12 | Hamza | 9 | 11 | £29,813.00 | ||||
| 13 | Kerry | 3 | 12 | £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
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Point | Salary | Hours | Actual | Point | Salary | FT hours | 37 |
| 2 | Amira | 3 | 37 | 1 | £24,000.00 | ||||
| 3 | Ben | 5 | 22.5 | 2 | £24,800.00 | ||||
| 4 | Cara | 2 | 30 | 3 | £25,700.00 | ||||
| 5 | 4 | £26,600.00 | |||||||
| 6 | 5 | £27,500.00 |
=XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6)in C2. Amira is on point 3: £25,700.=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.=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
- The pay scale is in G1:H13. In C2, look up each person's salary from their point.
- 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.
- 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.
- The pay bands are in G1:H6, with the lowest salary in each band. In C2, look up the band each salary falls into.
- 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.