Calculating Employee Turnover to Date from 2 Table

[Data can be tough AP]

Hi All,

I am looking to see if there is a way to build an employee turnover calculation from 2 tables. Previously I would just arrange the columns in an exact order and do simple calculations, but trying to find something that would faster than aligning everything in exact rows. The goal being that for January, it would just be leavers/headcount, and then for each following period it would be the sum of each month / average of each month. I tried to do a combination of index and match but struggled to get the formula to move with the periods.

Is there a way to do a formula that could pick this data up without much sorting?

For example,

January: January Leavers / January Headcount
June: Sum of leavers January - June / Average January - June

Kind regards,

Antonis

 

[Cubist]

Note: Fictious data set does not match the image - try:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		1/1/24	2/1/24	3/1/24	4/1/24	5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24	12/1/24
3	HR	28	31	21	32	14	33	41	15	40	49	11	26
4	Finance	31	45	14	33	44	29	14	32	42	19	36	43
5	Accounts	29	50	48	39	41	49	47	40	32	49	26	48
6	Cus Ser	23	22	20	50	41	23	42	28	45	16	40	35
7
8
9
10
11		1/1/24	2/1/24	3/1/24	4/1/24	5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24	12/1/24
12	Accounts				1							2
13	Cus Ser
14	HR			3							1
15	Finance												10
16
17
18		1/1/24	2/1/24	3/1/24	4/1/24	5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24	12/1/24
19	Finance	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	26.86%
20	Cus Ser	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21	Accounts	0.00%	0.00%	0.00%	2.53%	2.48%	2.24%	2.18%	2.33%	2.67%	2.31%	8.67%	7.26%
22	HR	0.00%	0.00%	13.79%	11.16%	14.68%	11.23%	8.86%	12.28%	9.31%	9.85%	15.50%	15.44%
Sheet3

Cell Formulas
Range	Formula
B19:M22	B19	=LET(headcount,FILTER($B$3:$M$6,$A$3:$A$6=A19),leavers,FILTER($B$12:$M$15,$A$12:$A$15=A19),SCAN(0,leavers,SUM)/SCAN(0,headcount,AVERAGE))
Dynamic array formulas.