Explanation
In this example, the goal is to calculate the maximum value that occurs in a set of data on a given weekday (i.e. Monday, Tuesday, Wednesday, Thursday, Friday). In the current version of Excel, the simplest approach is to use the FILTER function. In older versions of Excel, you can use a traditional array formula based on the MAX function with the IF function. Both approaches are explained below.
FILTER function
The most straightforward way to solve this problem is with the FILTER function , the MAX function , and the TEXT function . In the example shown, the formula in cell F5 is:
=MAX(FILTER(values,TEXT(dates,"ddd")=F4))
Working from the inside out, the TEXT function is configured to compare the “day” for each date, abbreviated to three letters, with the day value in cell F4:
TEXT(dates,"ddd")=F4)
Here, the TEXT function is using the custom number format “mmm” to calculate day abbreviations for the dates. Because there are 12 dates in dates (B5:B16) the TEXT function returns an array of 12 abbreviations like this:
{"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri"}=F4
When this array is compared to cell F4, the result is an array that contains 12 TRUE and FALSE values:
{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
In this array, the TRUE values correspond to dates that are Thursdays in the data. This array is returned directly to the FILTER function as the include argument with the named range values (C5:C16) as the array argument. FILTER uses the array to “filter” the values and returns the three values that occur on Thursdays directly to the MAX function:
=MAX({85;95;89})
MAX then returns 95 as a final result. If the value in F4 is changed to another day, the formula calculates a new result.
Legacy Excel
In older versions of Excel without the FILTER function, you can use a traditional array formula based on the MAX function and the IF function:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel .
Working from the inside out, the TEXT function is used to extract a weekday value for each date:
=TEXT(dates,"ddd")
The text string “ddd” is a number format for abbreviated day names. Because there are 12 dates in B5:B16, the result is an array with 12 abbreviated day names, which is then compared to cell F4:
{"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri"}=F4
With “Thu” in F4, this operation results in another array that contains only TRUE and FALSE values:
{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
Note each TRUE corresponds to a Thursday. This array is returned to the IF function as the logical_test . IF then 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;95;FALSE;FALSE;FALSE;FALSE;85;FALSE})
Note the only values that survive the trip through IF are those associated with Thursdays, the other values are now FALSE. MAX automatically ignores FALSE values and returns the highest remaining value, 95.
With AGGREGATE
For a more geeky formula that does not require control + shift + enter in Legacy Excel, you can use the AGGREGATE function like this:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
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 Thursdays. The result of this operation 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.
Note: The reason this formula works in older versions of Excel without being entered as an array formula is that AGGREGATE, like the SUMPRODUCT function , can handle many array operations natively. In the future, this use of AGGREGATE will diminish since the new dynamic array engine in Excel makes this kind of workaround unnecessary.
MAXIFS function
You might wonder why we aren’t using the MAXIFS function , which can calculate a maximum value based on one or more criteria. The problem is that MAXIFS is a ranged-based function , and won’t accept an array from another function like TEXT. If you want to use MAXIFS, you could add a helper column to the data that calculates weekday abbreviations with TEXT, then use MAXIFS with the helper column as the criteria range.
Explanation
Note: If you are new to INDEX and MATCH, see: How to use INDEX and MATCH
In a standard configuration, the INDEX function retrieves a value at a given row and column. For example, to get the value at row 2 and column 3 in a given range:
=INDEX(range,2,3) // get value at row 2, column 3
However, INDEX has a special trick – the ability to retrieve entire columns and rows. The syntax involves supplying zero for the “other” argument. If you want an entire column, you supply row as zero. If you want an entire row, you supply column as zero:
=INDEX(data,0,n) // retrieve column n
=INDEX(data,n,0) // retrieve row n
In the example shown, we want to find the maximum value in a given column. The twist is that the column needs to be variable so it can be easily changed. In F5, the formula is:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Working from the inside out, we first use the MATCH function to get the “index” of the column requested in cell J4:
MATCH(J4,header,0) // get column index
With “Green” in J4, the MATCH function returns 3, since Green is the third value in the named range header . After MATCH returns a result, the formula can be simplified to this:
=MAX(INDEX(data,0,3))
With zero provided as the row_number , INDEX returns all values in column 3 of the named range data. The result is returned to the MAX function in an array like this:
=MAX({83;54;35;17;85;16;70;72;65;93;91})
And MAX returns the final result, 93.
Minimum value
To get the minimum value with a variable column, you can simply replace the MAX function with the MIN function. The formula in J6 is:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
With FILTER
The new FILTER function can also be used to solve this problem, since FILTER can filter data by row or by column. The trick is to construct a logical filter that will exclude other columns. COUNTIF works well in this case, but it must be configured “backwards”, with J4 as the range , and header for criteria :
=MAX(FILTER(data,COUNTIF(J4,header)))
After COUNTIF runs, we have:
=MAX(FILTER(data,{0,0,1,0,0}))
And FILTER delivers the 3rd column to MAX, same as the INDEX function above.
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
The MATCH function is again set up “backwards”, so that we get an array with 5 values that will serve as the logical filter. After ISNUMBER and MATCH run, we have:
=MAX(FILTER(data,{FALSE,FALSE,TRUE,FALSE,FALSE}))
And FILTER again delivers the 3rd column to MAX.
