silviosilver
New Member
- Joined
- Aug 12, 2015
- Messages
- 24
In this sheet, column H finds the weekly MAX from 01/02/1993 based on the OFFSET values given in columns F and G.
The formula in column H is =MAX(OFFSET($B$3,F2,0,1,1):OFFSET($B$3,G2,0,1,1))
The value in H2 is the MAX from 01/02/1993 to 05/02/1993 (calculated from data not shown below)
The value in H3 is the MAX from 08/02/1993 to 12/02/1993
and so on.
However, the data I have omits weekends and holidays, therefore some 5-day "weeks" have less than five days in them.
In the example below, 16/02/1993 is a Tuesday, not a Monday as my formula was expecting, therefore this particular 5-day week ends on the 22/02/1993, not 19/02/1993 as I would like it to.
These missing days are throwing off my results.
Is there some way I could dynamically update my OFFSET periods to adjust for weeks with less than five days in them?
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
The formula in column H is =MAX(OFFSET($B$3,F2,0,1,1):OFFSET($B$3,G2,0,1,1))
The value in H2 is the MAX from 01/02/1993 to 05/02/1993 (calculated from data not shown below)
The value in H3 is the MAX from 08/02/1993 to 12/02/1993
and so on.
However, the data I have omits weekends and holidays, therefore some 5-day "weeks" have less than five days in them.
In the example below, 16/02/1993 is a Tuesday, not a Monday as my formula was expecting, therefore this particular 5-day week ends on the 22/02/1993, not 19/02/1993 as I would like it to.
These missing days are throwing off my results.
Is there some way I could dynamically update my OFFSET periods to adjust for weeks with less than five days in them?
E | F | G | H | |
---|---|---|---|---|
2 | 1/02/1993 | 0 | 4 | $45,843.70 |
3 | 2/02/1993 | 5 | 9 | $45,500.00 |
4 | 3/02/1993 | 10 | 14 | $45,250.00 |
5 | 4/02/1993 | 15 | 19 | $45,812.50 |
6 | 5/02/1993 | 20 | 24 | $45,281.20 |
7 | 8/02/1993 | 25 | 29 | $46,593.70 |
8 | 9/02/1993 | 30 | 34 | $46,562.50 |
9 | 10/02/1993 | 35 | 39 | $47,156.20 |
10 | 11/02/1993 | 40 | 44 | $46,937.50 |
11 | 12/02/1993 | 45 | 49 | $47,593.70 |
12 | 16/02/1993 | 50 | 54 | $48,312.50 |
13 | 17/02/1993 | 55 | 59 | $47,312.50 |
14 | 18/02/1993 | 60 | 64 | $45,718.70 |
15 | 19/02/1993 | 65 | 69 | $46,468.70 |
16 | 22/02/1993 | 70 | 74 | $46,562.50 |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1