Calculating weekly average of non-homogeneous data

phmalu

New Member
Joined
Jun 21, 2017
Messages
48
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!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
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),"")
 
Solution

phmalu

New Member
Joined
Jun 21, 2017
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

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
Top