The example on this page shows a simple array formula. Working from the inside out, the expression:
C5:C12-D5:D12
Results in an array containing seven values:
{17;19;32;25;12;26;29;22}
Each number in the array is...

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

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

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

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

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

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

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

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

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

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

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