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
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) |