Max Weekend Value, display date value was seen

johnjacob,

Attached is a modified version of your posted workbook.

I created 2 named ranges to avoid errors when performing the Weekday() function.
The first named range is for the days and I named it rngDays. Here is the formula it uses:

=OFFSET(Sheet1!$B$3,,,ROWS(Sheet1!$B:$B)-COUNTBLANK(Sheet1!$B:$B)-1)

The second named range is for the values and I named it rngValues. here is the formula it uses:

=OFFSET(rngDays,,1)

In cell J6 and J8 are array formulas. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter.
The array formula in J6:

=MAX(IF(WEEKDAY(rngDays,2)>5,rngValues))

The array formula in J8:

=MIN(IF(WEEKDAY(rngDays,2)>5,rngValues))

Lastly, to get the corresponding dates, I used Index/Match. Index/Match is a regular formula.
For the weekend max:

=INDEX(rngDays,MATCH(1,INDEX((WEEKDAY(rngDays,2)>5)*(rngValues=J6),),0))

For the weekend min:

=INDEX(rngDays,MATCH(1,INDEX((WEEKDAY(rngDays,2)>5)*(rngValues=J8),),0))

For the Month Max:

=INDEX(rngDays,MATCH(F6,rngValues,0))

For the Month Min:

=INDEX(rngDays,MATCH(F8,rngValues,0))

You're very welcome

To create a named range, you can go to the Formulas tab on the ribbon and then click Name Manager.

Here's some links to information on creating dynamic named ranges:
http://support.microsoft.com/kb/830287
http://office.microsoft.com/en-us/ex...001126115.aspx
http://www.contextures.com/xlNames01.html#Dynamic
http://www.cpearson.com/Excel/excelF.htm#DynamicRanges