Max value on given weekday

To find the maximum value on a given weekday (i.e. Monday, Tuesday, Wednesday, etc.) you can use a simple array formula based on the MAX, IF, and TEXT functions. In the example shown, the formula in cell F5 is:

=MAX(IF(TEXT(dates,"ddd")=F4,values))

Where dates (B5:B15) and values (C5:C15) are named ranges.

Note: this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

Working from the inside out, the TEXT function is used to extract a weekday value for each date:

=TEXT(dates,"ddd")

This results in an array like this:

{"Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon"}

which is then compared to the text in F4, "Mon". The result is another array, which contains only TRUE and FALSE values:

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Note each TRUE corresponds to a Monday. This array is returned inside the IF function as the logical test. It acts as a filter to screen out values on other days of the week. The final result from IF, which is returned directly to the MAX function, looks like this:

=MAX({85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52})

MAX automatically ignores FALSE values and returns the highest remaining value, 94.

With AGGREGATE

For a slighly more geeky formula that doesn't require control + shift + enter, you can use the AGGREGATE function like this:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)

This is the formula used in cell F6 in the example shown. Here we give AGGREGATE 14 for the function argument (LARGE) and 6 for option argument (ignore errors). Then we build a logical expression using the TEXT function to check all dates for Mondays. The result of this operation is is an array of TRUE/FALSE values, which become the denominator of the original values. When used in a math operation, FALSE evaluates as zero, and throws a #DIV/0! error. TRUE evaluates as 1 and returns the original value. The final array of values and errors acts like a filter. AGGREGATE ignores all errors and returns the largest (maximum) of the surviving values.

MAXIFS

The MAXIFS function, available in Excel Office 365, can return a max value using on one or more criteria without the need for an array formula. However, MAXIFS is a ranged-based function, and won't allow other functions like TEXT to process values in criteria ranges. You could however add a helper column to the data, generate weekday values with TEXT, then use MAXIFS with the helper column as a criteria range.