Calculating weekly average of non-homogeneous data

[phmalu]

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
	A	B	C	D	E	F	G	H
4	Date	Day	Month	Year	Price (R$)	Price (US$)	Weekly average (R$)	Weekly average (US$)
5	29/07/1997	29	7	1997	18,04	16,66	18,06	16,68
6	30/07/1997	30	7	1997	17,97	16,59
7	31/07/1997	31	7	1997	18,17	16,78
8	01/08/1997	1	8	1997	18,1	16,71	18,14	16,74
9	04/08/1997	4	8	1997	18,23	16,83
10	05/08/1997	5	8	1997	18,16	16,75
11	06/08/1997	6	8	1997	18,15	16,74
12	07/08/1997	7	8	1997	18,07	16,67
13	08/08/1997	8	8	1997	18,05	16,62	17,99	16,57
14	11/08/1997	11	8	1997	17,85	16,44
15	12/08/1997	12	8	1997	17,99	16,57
16	13/08/1997	13	8	1997	18	16,57
17	14/08/1997	14	8	1997	18,07	16,64
18	15/08/1997	15	8	1997	18,15	16,7	18,20	16,73
19	18/08/1997	18	8	1997	18,17	16,69
20	19/08/1997	19	8	1997	18,25	16,75
21	20/08/1997	20	8	1997	18,24	16,76
22	22/08/1997	22	8	1997	18,35	16,82	18,63	17,06
23	25/08/1997	25	8	1997	18,48	16,93
24	26/08/1997	26	8	1997	18,53	16,96
25	27/08/1997	27	8	1997	18,64	17,07
26	28/08/1997	28	8	1997	18,77	17,19
27	29/08/1997	29	8	1997	18,99	17,39
28	01/09/1997	1	9	1997	19,09	17,49	19,26	17,64
29	02/09/1997	2	9	1997	19,21	17,59
30	03/09/1997	3	9	1997	19,29	17,66
31	04/09/1997	4	9	1997	19,32	17,69
32	05/09/1997	5	9	1997	19,37	17,75
Plan 1

Cell Formulas
Range	Formula
B5:B32	B5	=DAY(A5)
C5:C32	C5	=MONTH(A5)
D5:D32	D5	=YEAR(A5)
G5:H5	G5	=AVERAGE(E5:E7)
G28:H28,G13:H13,G8:H8	G8	=AVERAGE(E8:E12)
G18:H18	G18	=AVERAGE(E18:E21)
G22:H22	G22	=AVERAGE(E22:E27)

Thank you in advance!

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
4	Date	Day	Month	Year	Price (R$)	Price (US$)	Weekly average (R$)	Weekly average (US$)
5	7/29/1997	29	7	1997	18.04	16.66	18.06	16.67667
6	7/30/1997	30	7	1997	17.97	16.59
7	7/31/1997	31	7	1997	18.17	16.78
8	8/1/1997	1	8	1997	18.1	16.71	18.142	16.74
9	8/4/1997	4	8	1997	18.23	16.83
10	8/5/1997	5	8	1997	18.16	16.75
11	8/6/1997	6	8	1997	18.15	16.74
12	8/7/1997	7	8	1997	18.07	16.67
13	8/8/1997	8	8	1997	18.05	16.62	17.992	16.568
14	8/11/1997	11	8	1997	17.85	16.44
15	8/12/1997	12	8	1997	17.99	16.57
16	8/13/1997	13	8	1997	18	16.57
17	8/14/1997	14	8	1997	18.07	16.64
18	8/15/1997	15	8	1997	18.15	16.7	18.2025	16.725
19	8/18/1997	18	8	1997	18.17	16.69
20	8/19/1997	19	8	1997	18.25	16.75
21	8/20/1997	20	8	1997	18.24	16.76
22	8/22/1997	22	8	1997	18.35	16.82	18.62667	17.06
23	8/25/1997	25	8	1997	18.48	16.93
24	8/26/1997	26	8	1997	18.53	16.96
25	8/27/1997	27	8	1997	18.64	17.07
26	8/28/1997	28	8	1997	18.77	17.19
27	8/29/1997	29	8	1997	18.99	17.39
28	9/1/1997	1	9	1997	19.09	17.49	19.256	17.636
29	9/2/1997	2	9	1997	19.21	17.59
30	9/3/1997	3	9	1997	19.29	17.66
31	9/4/1997	4	9	1997	19.32	17.69
32	9/5/1997	5	9	1997	19.37	17.75
Sheet17

Cell Formulas
Range	Formula
G5:H32	G5	=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),"")

 

[phmalu]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
4	Date	Day	Month	Year	Price (R$)	Price (US$)	Weekly average (R$)	Weekly average (US$)
5	7/29/1997	29	7	1997	18.04	16.66	18.06	16.67667
6	7/30/1997	30	7	1997	17.97	16.59
7	7/31/1997	31	7	1997	18.17	16.78
8	8/1/1997	1	8	1997	18.1	16.71	18.142	16.74
9	8/4/1997	4	8	1997	18.23	16.83
10	8/5/1997	5	8	1997	18.16	16.75
11	8/6/1997	6	8	1997	18.15	16.74
12	8/7/1997	7	8	1997	18.07	16.67
13	8/8/1997	8	8	1997	18.05	16.62	17.992	16.568
14	8/11/1997	11	8	1997	17.85	16.44
15	8/12/1997	12	8	1997	17.99	16.57
16	8/13/1997	13	8	1997	18	16.57
17	8/14/1997	14	8	1997	18.07	16.64
18	8/15/1997	15	8	1997	18.15	16.7	18.2025	16.725
19	8/18/1997	18	8	1997	18.17	16.69
20	8/19/1997	19	8	1997	18.25	16.75
21	8/20/1997	20	8	1997	18.24	16.76
22	8/22/1997	22	8	1997	18.35	16.82	18.62667	17.06
23	8/25/1997	25	8	1997	18.48	16.93
24	8/26/1997	26	8	1997	18.53	16.96
25	8/27/1997	27	8	1997	18.64	17.07
26	8/28/1997	28	8	1997	18.77	17.19
27	8/29/1997	29	8	1997	18.99	17.39
28	9/1/1997	1	9	1997	19.09	17.49	19.256	17.636
29	9/2/1997	2	9	1997	19.21	17.59
30	9/3/1997	3	9	1997	19.29	17.66
31	9/4/1997	4	9	1997	19.32	17.69
32	9/5/1997	5	9	1997	19.37	17.75
Sheet17

Cell Formulas
Range	Formula
G5:H32	G5	=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.

 

[Eric W]

Happy to help! Thanks for the update.