Calculating weekly average of non-homogeneous data

phmalu

Board Regular
Joined
Jun 21, 2017
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
I'm working with a 20 year historic price list for a specific product and I'd like to calculate the average for the first, second, third and forth week of each month.
However, this data list has a few missing days so I cannot simply calculate based on the number of cells. Instead, I need to figure out one formula that would do that for me.
Consider "first week" being from 1st to 7; 2nd being 8-14; 3rd 15-21 and 4th from 22 until the last day of that month.
In this short example I'm calculating it by hand just so you can understand better what exactly I need to calculate. However, the original sheet has 5,000+ rows which would make it VERY time-consuming to do it all manually.

CEPEA_20201109141931.xls
ABCDEFGH
4DateDayMonthYearPrice (R$)Price (US$)Weekly average (R$)Weekly average (US$)
529/07/1997297199718,0416,6618,0616,68
630/07/1997307199717,9716,59
731/07/1997317199718,1716,78
801/08/199718199718,116,7118,1416,74
904/08/199748199718,2316,83
1005/08/199758199718,1616,75
1106/08/199768199718,1516,74
1207/08/199778199718,0716,67
1308/08/199788199718,0516,6217,9916,57
1411/08/1997118199717,8516,44
1512/08/1997128199717,9916,57
1613/08/199713819971816,57
1714/08/1997148199718,0716,64
1815/08/1997158199718,1516,718,2016,73
1918/08/1997188199718,1716,69
2019/08/1997198199718,2516,75
2120/08/1997208199718,2416,76
2222/08/1997228199718,3516,8218,6317,06
2325/08/1997258199718,4816,93
2426/08/1997268199718,5316,96
2527/08/1997278199718,6417,07
2628/08/1997288199718,7717,19
2729/08/1997298199718,9917,39
2801/09/199719199719,0917,4919,2617,64
2902/09/199729199719,2117,59
3003/09/199739199719,2917,66
3104/09/199749199719,3217,69
3205/09/199759199719,3717,75
Plan 1
Cell Formulas
RangeFormula
B5:B32B5=DAY(A5)
C5:C32C5=MONTH(A5)
D5:D32D5=YEAR(A5)
G5:H5G5=AVERAGE(E5:E7)
G28:H28,G13:H13,G8:H8G8=AVERAGE(E8:E12)
G18:H18G18=AVERAGE(E18:E21)
G22:H22G22=AVERAGE(E22:E27)


Thank you in advance!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try:

Book1 (version 1).xlsb
ABCDEFGH
4DateDayMonthYearPrice (R$)Price (US$)Weekly average (R$)Weekly average (US$)
57/29/1997297199718.0416.6618.0616.67667
67/30/1997307199717.9716.59  
77/31/1997317199718.1716.78  
88/1/199718199718.116.7118.14216.74
98/4/199748199718.2316.83  
108/5/199758199718.1616.75  
118/6/199768199718.1516.74  
128/7/199778199718.0716.67  
138/8/199788199718.0516.6217.99216.568
148/11/1997118199717.8516.44  
158/12/1997128199717.9916.57  
168/13/199713819971816.57  
178/14/1997148199718.0716.64  
188/15/1997158199718.1516.718.202516.725
198/18/1997188199718.1716.69  
208/19/1997198199718.2516.75  
218/20/1997208199718.2416.76  
228/22/1997228199718.3516.8218.6266717.06
238/25/1997258199718.4816.93  
248/26/1997268199718.5316.96  
258/27/1997278199718.6417.07  
268/28/1997288199718.7717.19  
278/29/1997298199718.9917.39  
289/1/199719199719.0917.4919.25617.636
299/2/199729199719.2117.59  
309/3/199739199719.2917.66  
319/4/199749199719.3217.69  
329/5/199759199719.3717.75  
Sheet17
Cell Formulas
RangeFormula
G5:H32G5=IF(MATCH(DAY($A5),{0,1,8,15,22})<>MATCH(DAY(N($A4)),{0,1,8,15,22}),AVERAGEIF($A5:$A14,"<="&IF(MATCH(DAY($A5),{0,1,8,15,22})=5,EOMONTH($A5,0),$A5+MOD(7-MOD(DAY($A5),7),7)),E5:E14),"")
 
Upvote 0
Solution
Try:

Book1 (version 1).xlsb
ABCDEFGH
4DateDayMonthYearPrice (R$)Price (US$)Weekly average (R$)Weekly average (US$)
57/29/1997297199718.0416.6618.0616.67667
67/30/1997307199717.9716.59  
77/31/1997317199718.1716.78  
88/1/199718199718.116.7118.14216.74
98/4/199748199718.2316.83  
108/5/199758199718.1616.75  
118/6/199768199718.1516.74  
128/7/199778199718.0716.67  
138/8/199788199718.0516.6217.99216.568
148/11/1997118199717.8516.44  
158/12/1997128199717.9916.57  
168/13/199713819971816.57  
178/14/1997148199718.0716.64  
188/15/1997158199718.1516.718.202516.725
198/18/1997188199718.1716.69  
208/19/1997198199718.2516.75  
218/20/1997208199718.2416.76  
228/22/1997228199718.3516.8218.6266717.06
238/25/1997258199718.4816.93  
248/26/1997268199718.5316.96  
258/27/1997278199718.6417.07  
268/28/1997288199718.7717.19  
278/29/1997298199718.9917.39  
289/1/199719199719.0917.4919.25617.636
299/2/199729199719.2117.59  
309/3/199739199719.2917.66  
319/4/199749199719.3217.69  
329/5/199759199719.3717.75  
Sheet17
Cell Formulas
RangeFormula
G5:H32G5=IF(MATCH(DAY($A5),{0,1,8,15,22})<>MATCH(DAY(N($A4)),{0,1,8,15,22}),AVERAGEIF($A5:$A14,"<="&IF(MATCH(DAY($A5),{0,1,8,15,22})=5,EOMONTH($A5,0),$A5+MOD(7-MOD(DAY($A5),7),7)),E5:E14),"")
Works flawlessly!
Thank you very much.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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