12m Rolling Average Across Columns

holy

New Member
Joined
Jan 9, 2023
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWX
1wkdatebar 120222019bar 220222019bar 320222019
2Week EndingWeek No1108/01/202310,018.6919,234.512,313.928,267.116,107.6012,242.37
3215/01/202311,044.8922,071.205,300.869,348.258,774.6212,660.04
4322/01/202311,637.7131,333.444,839.409,204.059,716.5515,352.02
5ROLLING AVERAGE (12 months)429/01/202322,296.0626,810.276,639.4110,421.2911,948.7715,292.71
620232022%L4L505/02/202318,279.4731,390.476,256.779,456.8312,677.5016,338.55
71Bar 1#DIV/0!612/02/202322,290.2129,800.658,830.289,671.4910,323.4216,523.77
82Bar 2#DIV/0!719/02/202323,316.5628,541.647,839.1910,078.9914,329.5615,611.34
93Bar 3#DIV/0!826/02/202323,858.4332,383.609,136.979,930.3714,196.0615,968.64
10TOTAL£0.00£0.00#DIV/0!905/03/202326,114.5231,459.319,550.6112,558.9316,446.7317,439.68
111012/03/202328,851.6232,660.608,268.119,403.9313,324.6610,149.13
121119/03/202322,776.8829,202.208,175.3111,771.8113,479.1917,756.97
131226/03/202320,223.8930,239.458,019.6610,527.2223,789.8515,901.74
141302/04/202323,099.6029,507.388,186.9310,199.6516,396.8016,331.60
151409/04/202331,253.3331,571.008,356.1311,484.4414,436.6115,274.01
161516/04/202331,423.4526,455.179,979.4810,923.5916,044.9827,108.34
171623/04/202331,175.7228,140.438,492.558,031.1824,530.9323,841.23
181730/04/202326,201.9425,151.968,504.019,692.4318,420.3518,204.76
191807/05/202328,725.7023,872.958,547.019,298.0517,997.6119,850.09
201914/05/202325,436.0527,401.527,299.389,473.4716,401.1617,649.61
212021/05/202310,018.6925,777.352,313.926,767.2410,018.6922,052.89
222128/05/202311,044.8930,948.285,300.868,906.1111,044.8918,502.33
232204/06/202311,637.7127,023.954,839.4011,808.8711,637.7115,909.22
242311/06/202322,296.0624,229.446,639.4112,499.7022,296.0614,959.22
252418/06/202318,279.4722,951.766,256.777,974.4418,279.4715,934.83
262525/06/202322,290.2128,283.308,830.288,166.7622,290.2116,906.76
272602/07/202323,316.5626,429.677,839.197,873.9323,316.5622,061.27
282709/07/202323,858.4324,906.469,136.977,447.0623,858.4318,781.45
292816/07/202326,114.5220,430.409,550.617,348.0026,114.5218,429.15
302923/07/202328,851.6220,984.918,268.116,709.3528,851.6219,322.25
313030/07/202322,776.8825,602.568,175.317,335.9022,776.8820,510.79
323106/08/202320,223.8924,732.878,019.666,970.4420,223.8920,531.44
333213/08/202323,099.6022,538.518,186.938,615.9223,099.6020,379.53
343320/08/202331,253.3326,767.368,356.138,958.8731,253.3320,251.14
353427/08/202331,423.4526,370.149,979.488,700.2331,423.4517,698.27
363503/09/202331,175.7232,156.428,492.559,427.7031,175.7219,365.43
373610/09/202326,201.9428,367.388,504.018,427.8926,201.9417,439.43
383717/09/202328,725.7028,813.518,547.0113,713.6528,725.7015,336.20
393824/09/202325,436.0531,945.027,299.389,129.3225,436.0520,505.02
403901/10/202310,018.6929,543.708,268.1112,061.5110,018.6916,558.67
414008/10/202311,044.8931,584.348,175.319,314.8311,044.8915,945.81
424115/10/202311,637.7134,895.948,019.6610,162.0211,637.7113,949.26
434222/10/202322,296.0632,781.148,186.9310,168.5222,296.0618,240.63
444329/10/202318,279.4732,947.908,356.1311,426.1218,279.4717,789.07
454405/11/202322,290.2133,243.299,979.4810,813.1722,290.2117,593.60
464512/11/202323,316.5633,015.988,492.5511,602.6623,316.5617,306.72
474619/11/202323,858.4334,620.638,504.0111,658.7423,858.4314,438.87
484726/11/202326,114.5234,177.028,547.0112,149.0926,114.5213,966.92
494803/12/202328,851.6230,207.847,299.3813,725.2228,851.6217,734.94
504910/12/202311,044.8939,340.169,979.4812,145.4711,044.8912,809.28
515017/12/202311,637.7132,268.318,492.5511,199.8711,637.7112,352.04
525124/12/202322,296.0635,485.928,504.0114,666.6622,296.0616,786.37
535231/12/202318,279.4726,102.919,979.489,037.4918,279.479,814.06
OVERVIEW
Cell Formulas
RangeFormula
E7:E10E7=SUM((C7-D7)/D7)
C10:D10C10=SUM(C7:C9)
 
Hi & welcome to MrExcel.
You don't have any data for 2023, just 2019 & 2022. ;)
 
Upvote 0
Sorry. the Bar name is 2023 (so columns N, R & V)
 
Upvote 0
Ok, for Bar1 how about
Excel Formula:
=(SUMIFS(O:O,K:K,">="&H2)+SUMIFS(N:N,K:K,"<"&H2))/52
 
Upvote 0
Solution
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,868
Messages
6,193,435
Members
453,799
Latest member
shanley ducker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top