Adjust OFFSET length for holidays

[silviosilver]

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?

​

	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1/02/1993[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$45,843.70[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/02/1993[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$45,500.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/02/1993[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$45,250.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4/02/1993[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]$45,812.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/02/1993[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$45,281.20[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8/02/1993[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]$46,593.70[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9/02/1993[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]$46,562.50[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10/02/1993[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]$47,156.20[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]11/02/1993[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]$46,937.50[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12/02/1993[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]$47,593.70[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]16/02/1993[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]$48,312.50[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]17/02/1993[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]$47,312.50[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]18/02/1993[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]$45,718.70[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]19/02/1993[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]$46,468.70[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]22/02/1993[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]$46,562.50[/TD]

</tbody>

Sheet1​

 

[StephenCrump]

Depending on your version of Excel:

E4:=MAXIFS(B$2:B$18,A$2:A$18,"<="& D4,A$2:A$18,">="&D4-4)

OR

E4: {=MAX(IF((A$2:A$18<=D4)*(A$2:A$18>=D4-4),B$2:B$18))} array-entered

Excel 2010
	A	B	C	D	E
1	Date	Value		Maximum for
2	Mon 13 Nov 2017	9		week ending
3	Tue 14 Nov 2017	14
4	Wed 15 Nov 2017	3		Fri 17 Nov 2017	14
5	Thu 16 Nov 2017	4		Fri 24 Nov 2017	19
6	Fri 17 Nov 2017	4		Fri 1 Dec 2017	13
7	Tue 21 Nov 2017	8		Fri 8 Dec 2017	20
8	Wed 22 Nov 2017	19
9	Thu 23 Nov 2017	5
10	Mon 27 Nov 2017	13
11	Tue 28 Nov 2017	8
12	Wed 29 Nov 2017	12
13	Thu 30 Nov 2017	12
14	Mon 4 Dec 2017	9
15	Tue 5 Dec 2017	20
16	Wed 6 Dec 2017	12
17	Thu 7 Dec 2017	5
18	Fri 8 Dec 2017	3
Sheet1