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";...

The MAX function is fully automatic – it returns the largest value in the numbers provided. In this case, we give MAX function two values:
=MAX(B5,C5)
and MAX returns the larger value.
Elegant alternative to IF...

The IF function is evaluated first. The logical test is an expression that tests all names:
IF(names=F6 // logical test
The result is an array of TRUE / FALSE values like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;...

The MAXIFS function can find the maximum value in a range based on one or more criteria. In the example shown, we are using MAXIFS to find the max sales value based in a given month by "bracketing" dates between the...

XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date. If data were sorted by date in ascending order, this would be...

At the core, this formula runs two tests on a value like this:
=D5>MIN(B5,C5) // is D5 greater than smaller?
=D5<MAX(B5,C5)) // is D5 less than larger?
In the first expression, the value is compared to the...

This example uses the following named ranges: "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. The goal is to find the maximum price for a given color and item.
This formula uses two nested IF functions,...

In this example, we first calculate the total hours between the start time and end time. Then we figure out the total hours that overlap "the period of interest". Using these two values, we can figure out all remaining...

The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria.
Like COUNTIFS and SUMIFS, these functions...

This formula uses the MOD and COLUMN functions to filter out values not in nth columns, then runs MAX on the result.
The key is this snippet:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Here, the formula uses the COLUMN...

This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values.
Inside MAX, the value in B5 is subtracted from the value in C5. If the result is positive, MAX will...

The MAX function accepts one or more arguments, which can be a mix of constants, cell references, and ranges. MAX then returns the maximum value in the data provided. Text values and empty cells are ignored.
In this...

The MAX function first extracts the maximum value from the range C3:C11. In this case, that value is 849900.
This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range...

This is a tricky formula to understand, and it requires that you have a numeric id for each match, and that all matches are sorted by id.
They key is in understanding how FREQUENCY gathers numbers into "bins". Each...

The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the...