I'm trying to work out an HR calculation (vacancy rate) used in someone's spreadsheet (they've left company). Hard values have been used.

[maxim642]

I have been presented with a spreadsheet to complete for monthly reporting submissions. The owner of said spreadsheet has left the business and I'm trying to work out how they arrived at some of their numbers. I've managed to do so for every section other than the last (vacancy rate). Based on the dataset, can any of you spot the calculation needed based on the inputs for October, November & December? I'm sure it's obvious to someone...

6.4.21 HR Dashboard ML.xlsx
	A	B	C	D	E	F	G	H
1	Entity	Metric	Format		Oct-20	Nov-20	Dec-20	Jan-21
2
3	ABC Group	Voluntary turnover rate	%		7.25	6.89	7.60	6.24%
4	ABC	Voluntary turnover rate	%		7.85	7.41	8.29	6.64%
5	ABC Clear	Voluntary turnover rate	%		4.51	4.46	4.41	4.37%
6
7	ABC Group	Absence Rate	%		1.77	1.89	1.85	1.71%
8	ABC	Absence Rate	%		1.89	2.05	2.02	1.91%
9	ABC Clear	Absence Rate	%		1.25	1.11	1.04	0.80%
10
11	ABC Group	Open Positions	raw number		12.00	12.00	6.00	8.00
12	ABC	Open Positions	raw number		8.00	8.00	6.00	8.00
13	ABC Clear	Open Positions	raw number		4.00	4.00	0.00	0.00
14
15	ABC Group	Open Positions	%		3.09	3.06	1.58	2.1%
16	ABC	Open Positions	%		2.51	2.48	1.92	2.5%
17	ABC Clear	Open Positions	%		5.80	5.80	0.00	0.0%
18
19	ABC Group	Headcount	raw number		388.00	392.00	380.00	388.00
20	ABC	Headcount	raw number		319.00	323.00	312.00	316.00
21	ABC Clear	Headcount	raw number		69.00	69.00	68.00	72.00
22
23	ABC Group	Average Headcount	raw number		372.33	377.58	381.58	384.75
24	ABC	Average Headcount	raw number		305.83	310.25	313.50	316.17
25	ABC Clear	Average Headcount	raw number		66.50	67.33	68.08	68.58
26
27	ABC Group	Contractor Headcount	raw number		121.00	121.00	119.00	119.00
28	ABC	Contractor Headcount	raw number		103.00	104.00	102.00	105.00
29	ABC Clear	Contractor Headcount	raw number		18.00	17.00	17.00	14.00
30
31	ABC Group	Contractor Headcount	%		23.77	23.59	23.85	30.67%
32	ABC	Contractor Headcount	%		24.41	24.36	24.64	33.23%
33	ABC Clear	Contractor Headcount	%		20.69	19.77	20.00	19.44%
34
35	ABC Group	Leavers	raw number		27.00	26.00	29.00	24.00
36	ABC	Leavers	raw number		24.00	23.00	26.00	21.00
37	ABC Clear	Leavers	raw number		3.00	3.00	3.00	3.00
38
39	ABC Group	Unplanned absence	Days		1,491.50	1,609.50	1,592.50	1,490.50
40	ABC	Unplanned absence	Days		1,303.00	1,440.50	1,433.00	1,366.50
41	ABC Clear	Unplanned absence	Days		188.50	169.00	159.50	124.00
42
43	ABC Group	Total WorkDays	raw number		84,147.33	85,333.83	86,237.83	86,953.50
44	ABC	Total WorkDays	raw number		69,118.33	70,116.50	70,851.00	71,453.67
45	ABC Clear	Total WorkDays	raw number		15,029.00	15,217.33	15,386.83	15,499.83
46
47	ABC Group	Vacancy Rate	%		3.00	2.97	1.55
48	ABC	Vacancy Rate	%		2.45	2.42	1.89
49	ABC Clear	Vacancy Rate	%		5.48	5.48	0.00
Sheet3

Cell Formulas
Range	Formula
H3:H5	H3	=H35/H23
H7:H9	H7	=H39/H43
H15:H17	H15	=H11/H19
H31:H33	H31	=H27/H19

 

[KRice]

See the green cells:

MrExcel20210407.xlsx
	A	B	C	D	E	F	G	H
47	ABC Group	Vacancy Rate	%		3	2.970297	1.554404
48	ABC	Vacancy Rate	%		2.446483	2.416918	1.886792
49	ABC Clear	Vacancy Rate	%		5.479452	5.479452	0
50
51					3.0000%	2.9703%	1.5544%	2.0202%
52					2.4465%	2.4169%	1.8868%	2.4691%
53					5.4795%	5.4795%	0.0000%	0.0000%
Sheet2

Cell Formulas
Range	Formula
E51:H53	E51	=E11/(E11+E19)

 

[maxim642]

See the green cells:

MrExcel20210407.xlsx
	A	B	C	D	E	F	G	H
47	ABC Group	Vacancy Rate	%		3	2.970297	1.554404
48	ABC	Vacancy Rate	%		2.446483	2.416918	1.886792
49	ABC Clear	Vacancy Rate	%		5.479452	5.479452	0
50
51					3.0000%	2.9703%	1.5544%	2.0202%
52					2.4465%	2.4169%	1.8868%	2.4691%
53					5.4795%	5.4795%	0.0000%	0.0000%
Sheet2

Cell Formulas
Range	Formula
E51:H53	E51	=E11/(E11+E19)

Thanks mate

 

[KRice]

I'm happy to help...thanks for the feedback.