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
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​

 

[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