12m Rolling Average Across Columns

[holy]

Got a funny feeling this is a super easy one, but I can't quite get my head around it.

I have a small summary table (on the left) that I'd like to show rolling 12 month averages based on the week number (in H2).

The data is split by year across columns which makes the 12 month a little messy and I'm getting confused. I'm guessing it's a combo of AVERAGE & OFFSET across the two columns but I'm flummoxed.

For example - on Wk No 1 we'd need the last 52 weeks average (which would be week 1 - 52 from 2022 - easy!) but on Wk No 12 we'd need 11 weeks from 2023 column and 41 weeks from 2022 column.

Help appreciated!
c

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1											wk	date		bar 1	2022	2019		bar 2	2022	2019		bar 3	2022	2019
2		Week Ending					Week No	1			1	08/01/2023			10,018.69	19,234.51			2,313.92	8,267.11			6,107.60	12,242.37
3											2	15/01/2023			11,044.89	22,071.20			5,300.86	9,348.25			8,774.62	12,660.04
4											3	22/01/2023			11,637.71	31,333.44			4,839.40	9,204.05			9,716.55	15,352.02
5			ROLLING AVERAGE (12 months)								4	29/01/2023			22,296.06	26,810.27			6,639.41	10,421.29			11,948.77	15,292.71
6			2023	2022	%L4L						5	05/02/2023			18,279.47	31,390.47			6,256.77	9,456.83			12,677.50	16,338.55
7	1	Bar 1			#DIV/0!						6	12/02/2023			22,290.21	29,800.65			8,830.28	9,671.49			10,323.42	16,523.77
8	2	Bar 2			#DIV/0!						7	19/02/2023			23,316.56	28,541.64			7,839.19	10,078.99			14,329.56	15,611.34
9	3	Bar 3			#DIV/0!						8	26/02/2023			23,858.43	32,383.60			9,136.97	9,930.37			14,196.06	15,968.64
10		TOTAL	£0.00	£0.00	#DIV/0!						9	05/03/2023			26,114.52	31,459.31			9,550.61	12,558.93			16,446.73	17,439.68
11											10	12/03/2023			28,851.62	32,660.60			8,268.11	9,403.93			13,324.66	10,149.13
12											11	19/03/2023			22,776.88	29,202.20			8,175.31	11,771.81			13,479.19	17,756.97
13											12	26/03/2023			20,223.89	30,239.45			8,019.66	10,527.22			23,789.85	15,901.74
14											13	02/04/2023			23,099.60	29,507.38			8,186.93	10,199.65			16,396.80	16,331.60
15											14	09/04/2023			31,253.33	31,571.00			8,356.13	11,484.44			14,436.61	15,274.01
16											15	16/04/2023			31,423.45	26,455.17			9,979.48	10,923.59			16,044.98	27,108.34
17											16	23/04/2023			31,175.72	28,140.43			8,492.55	8,031.18			24,530.93	23,841.23
18											17	30/04/2023			26,201.94	25,151.96			8,504.01	9,692.43			18,420.35	18,204.76
19											18	07/05/2023			28,725.70	23,872.95			8,547.01	9,298.05			17,997.61	19,850.09
20											19	14/05/2023			25,436.05	27,401.52			7,299.38	9,473.47			16,401.16	17,649.61
21											20	21/05/2023			10,018.69	25,777.35			2,313.92	6,767.24			10,018.69	22,052.89
22											21	28/05/2023			11,044.89	30,948.28			5,300.86	8,906.11			11,044.89	18,502.33
23											22	04/06/2023			11,637.71	27,023.95			4,839.40	11,808.87			11,637.71	15,909.22
24											23	11/06/2023			22,296.06	24,229.44			6,639.41	12,499.70			22,296.06	14,959.22
25											24	18/06/2023			18,279.47	22,951.76			6,256.77	7,974.44			18,279.47	15,934.83
26											25	25/06/2023			22,290.21	28,283.30			8,830.28	8,166.76			22,290.21	16,906.76
27											26	02/07/2023			23,316.56	26,429.67			7,839.19	7,873.93			23,316.56	22,061.27
28											27	09/07/2023			23,858.43	24,906.46			9,136.97	7,447.06			23,858.43	18,781.45
29											28	16/07/2023			26,114.52	20,430.40			9,550.61	7,348.00			26,114.52	18,429.15
30											29	23/07/2023			28,851.62	20,984.91			8,268.11	6,709.35			28,851.62	19,322.25
31											30	30/07/2023			22,776.88	25,602.56			8,175.31	7,335.90			22,776.88	20,510.79
32											31	06/08/2023			20,223.89	24,732.87			8,019.66	6,970.44			20,223.89	20,531.44
33											32	13/08/2023			23,099.60	22,538.51			8,186.93	8,615.92			23,099.60	20,379.53
34											33	20/08/2023			31,253.33	26,767.36			8,356.13	8,958.87			31,253.33	20,251.14
35											34	27/08/2023			31,423.45	26,370.14			9,979.48	8,700.23			31,423.45	17,698.27
36											35	03/09/2023			31,175.72	32,156.42			8,492.55	9,427.70			31,175.72	19,365.43
37											36	10/09/2023			26,201.94	28,367.38			8,504.01	8,427.89			26,201.94	17,439.43
38											37	17/09/2023			28,725.70	28,813.51			8,547.01	13,713.65			28,725.70	15,336.20
39											38	24/09/2023			25,436.05	31,945.02			7,299.38	9,129.32			25,436.05	20,505.02
40											39	01/10/2023			10,018.69	29,543.70			8,268.11	12,061.51			10,018.69	16,558.67
41											40	08/10/2023			11,044.89	31,584.34			8,175.31	9,314.83			11,044.89	15,945.81
42											41	15/10/2023			11,637.71	34,895.94			8,019.66	10,162.02			11,637.71	13,949.26
43											42	22/10/2023			22,296.06	32,781.14			8,186.93	10,168.52			22,296.06	18,240.63
44											43	29/10/2023			18,279.47	32,947.90			8,356.13	11,426.12			18,279.47	17,789.07
45											44	05/11/2023			22,290.21	33,243.29			9,979.48	10,813.17			22,290.21	17,593.60
46											45	12/11/2023			23,316.56	33,015.98			8,492.55	11,602.66			23,316.56	17,306.72
47											46	19/11/2023			23,858.43	34,620.63			8,504.01	11,658.74			23,858.43	14,438.87
48											47	26/11/2023			26,114.52	34,177.02			8,547.01	12,149.09			26,114.52	13,966.92
49											48	03/12/2023			28,851.62	30,207.84			7,299.38	13,725.22			28,851.62	17,734.94
50											49	10/12/2023			11,044.89	39,340.16			9,979.48	12,145.47			11,044.89	12,809.28
51											50	17/12/2023			11,637.71	32,268.31			8,492.55	11,199.87			11,637.71	12,352.04
52											51	24/12/2023			22,296.06	35,485.92			8,504.01	14,666.66			22,296.06	16,786.37
53											52	31/12/2023			18,279.47	26,102.91			9,979.48	9,037.49			18,279.47	9,814.06
OVERVIEW

Cell Formulas
Range	Formula
E7:E10	E7	=SUM((C7-D7)/D7)
C10:D10	C10	=SUM(C7:C9)

 

[Fluff]

Hi & welcome to MrExcel.
You don't have any data for 2023, just 2019 & 2022.

 

[holy]

Sorry. the Bar name is 2023 (so columns N, R & V)

 

[Fluff]

Ok, for Bar1 how about

=(SUMIFS(O:O,K:K,">="&H2)+SUMIFS(N:N,K:K,"<"&H2))/52

 

[holy]

oh lawdy that was easy. I feel a bit dumb.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[holy]

Thanks for the help!