The MINIFS function is designed to return minimum values in a range based on one or more criteria. In this case, the range we are extracting a minimum value from is E5:E22), named "times".
We are filtering these...

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 bin...

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

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

The key to this formula is the MATCH function, which is set up like this:
MATCH(MAX(LEN(name)),LEN(name),0))
In this snippet, MATCH is set up to perform an exact match by supplying zero for match type. For lookup...

The gist of this formula is that we build a list of row numbers for a given range, matching on a value, and then use the MAX function to get the largest row number , which corresponds to the last matching value. We are...

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

The core of this formula is the MATCH function, which locates the position of the longest string using supplied criteria:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Note MATCH is set up to perform an...

In order to understand this problem, make sure you understand how percentage number formatting works. In a nutshell, percentages are decimal values: 0.1 is 10%, 0.2 is 20%, and so on. The number 1, when formatted as a...

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

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

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

Excel dates are just serial numbers, so you can calculate durations by subtracting the earlier date from the later date.
This is what happens at the core of the formula here:
MIN(end,C6)-MAX(start,B6)+1
Here are...

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