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:
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.
Working from the inside out, the TEXT function is used to extract a weekday value for each date:
This results in an array like this:
which is then compared to the text in F4, "Mon". The result is another array, which contains only TRUE and FALSE values:
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 automatically ignores FALSE values and returns the highest remaining value, 94.
For a slighly more geeky formula that doesn't require control + shift + enter, you can use the AGGREGATE function like this:
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.
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.