Finding MAX & Min for every week

[mark91345]

Using Excel 2013 (Windows 8.1), I am trying to figure out how to find the Min and Max for EACH WEEK's stock prices.
Note that sometimes, there is no "Monday" because of holidays. I hand-added the last two Min/Max columns to show what I'd like to see.Excel 2012

	A	B	C	D	E	F	G
Weekday	Weekly MIN	Weekly MAX
Monday
Tuesday
Wednesday
Thursday
Friday
Monday
Tuesday
Wednesday
Thursday
Friday
Tuesday
Wednesday
Thursday
Friday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: right"]Low[/TD]
[TD="align: right"]High[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]08/03/15[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]5.10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]08/04/15[/TD]
[TD="align: right"]4.73[/TD]
[TD="align: right"]4.95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]08/05/15[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]4.85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]08/06/15[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]4.96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]08/07/15[/TD]
[TD="align: right"]4.76[/TD]
[TD="align: right"]5.08[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]5.10[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]08/10/15[/TD]
[TD="align: right"]4.83[/TD]
[TD="align: right"]5.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]08/11/15[/TD]
[TD="align: right"]5.09[/TD]
[TD="align: right"]5.21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]08/12/15[/TD]
[TD="align: right"]4.95[/TD]
[TD="align: right"]5.16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]08/13/15[/TD]
[TD="align: right"]5.18[/TD]
[TD="align: right"]5.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]08/14/15[/TD]
[TD="align: right"]5.28[/TD]
[TD="align: right"]5.48[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.83[/TD]
[TD="align: right"]5.62[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]08/18/15[/TD]
[TD="align: right"]5.19[/TD]
[TD="align: right"]5.31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]08/19/15[/TD]
[TD="align: right"]5.20[/TD]
[TD="align: right"]5.35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]08/20/15[/TD]
[TD="align: right"]5.01[/TD]
[TD="align: right"]5.30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]08/21/15[/TD]
[TD="align: right"]4.85[/TD]
[TD="align: right"]5.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.85[/TD]
[TD="align: right"]5.35[/TD]

</tbody>

Sheet1​

 

[Teeroy]

Easiest way I can think of is to put in a helper column in E where;

E2: =WEEKNUM(B2)

then use the following formulas;

F2: =IF(E3<>E2,MIN(IF(($E$2:$E$15=E2)*($C$2:$C$15)=0,9.99E+306,($E$2:$E$15=E2)*($C$2:$C$15))),"")
G2: =IF(E3<>E2,MAX(($E$2:$E$15=E2)*($D$2:$D$15)),"")

Both as Array formulae (confirm with Ctrl + Shift + Enter NOT just Enter).
Then copy/Fill down E2:G2 to row 15