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.
Thank you in advance!
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!