Confident Cells

Pay gap: practice exercises

AVERAGEIF, MEDIAN with FILTER, QUARTILE, COUNTIFS. 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

In H1, work out the mean hourly pay of the men.

ABCDEFGH
1EmployeeSexHourly payMean pay, men
2GraceFemale£19.20Mean pay, women£23.6123
3JessFemale£21.79
4MariaMale£16.62Median pay, men£28.31
5JordanMale£34.21
6PriyaMale£38.39
7OllieFemale£33.36
8LiamMale£33.82
9ZoeMale£15.98
10IvanFemale£27.10
11TomFemale£19.88
12EllaFemale£18.97
13LucyFemale£24.48
14NadiaFemale£21.58
15OwenFemale£24.61
16RachelFemale£16.71
17ChenMale£32.05
18TanyaFemale£16.70
19NiaMale£24.57
20MarekMale£19.71
21FemiFemale£30.73
22SunilFemale£31.85

How it works

The gender pay gap compares the average hourly pay of men and women, as a percentage of men's pay: (men − women) ÷ men. Employers with 250 or more staff publish it each year, using both the mean and the median.

AVERAGEIF gives a mean for one group. There's no MEDIANIF, so give MEDIAN only that group's pay with FILTER: MEDIAN(FILTER(C2:C22,B2:B22="Female")). QUARTILE(range, 3) gives the upper quartile, the pay that a quarter of people are at or above.

=AVERAGEIF(B2:B7,"Female",C2:C7) =(E2-E5)/E2 =MEDIAN(FILTER(C2:C7,B2:B7="Male")) =QUARTILE(C2:C7,1)

The pay gap

The gender pay gap is the difference between men's and women's average hourly pay, as a share of the men's pay: (men − women) ÷ men. Employers with 250 or more staff publish both the mean and the median gap.

Worked example

ABC
1NameSexHourly pay
2AmirMale£14.00
3BethFemale£12.00
4CalMale£18.00
5DinaFemale£13.50
6EveFemale£15.00
7FinnMale£22.00
  1. =AVERAGEIF(B2:B7,"Male",C2:C7) in E2. The men's mean hourly pay: (14 + 18 + 22) ÷ 3 = £18. The women's is £13.50.
  2. =(AVERAGEIF(B2:B7,"Male",C2:C7)-AVERAGEIF(B2:B7,"Female",C2:C7))/AVERAGEIF(B2:B7,"Male",C2:C7) in E3. (£18 − £13.50) ÷ £18 = 25%. In a real sheet you'd point at the two cells with the means in, which is much shorter.
  3. =MEDIAN(FILTER(C2:C7,B2:B7="Female")) in E4. There's no MEDIANIF, so FILTER picks out the women's pay and MEDIAN finds the middle: £13.50.

Quartiles

QUARTILE(range, 3) is the upper quartile: a quarter of people are paid at or above it. Reports show what share of the top quarter are women.

That's women at or above the quartile, divided by everyone at or above it: COUNTIFS(sex,"Female",pay,">="&Q)/COUNTIF(pay,">="&Q).

What you'll practise

  1. In H1, work out the mean hourly pay of the men.
  2. In H3, work out the mean pay gap: how much less the women earn on average, as a percentage of the men's mean pay.
  3. In H5, work out the median hourly pay of the women.
  4. In H7, find the upper quartile of everyone's hourly pay.
  5. In H8, work out what percentage of the people paid at least the upper quartile (H7) are women.