When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for...

At a high level, this example is about finding a minimum value based on multiple criteria. To do that, we are using the MIN function together with two nested IF functions:
{=MIN(IF(day=I5,IF(tide="L",pred...

This formula uses the named range "list" which is the range B5:B11.
The core of this formula is the SMALL function, which simply returns the nth smallest value in a list of values that correspond to row numbers. The...

The first part of the solution uses the MIN and TODAY functions to find the "next date" based on the date today. This is done by filtering the dates through the IF function:
IF((date>=TODAY()),date)
The logical...

The formulas shown in the example all use the AVERAGE function with a relative reference set up for each specific interval. The 3-day moving average in E7 is calculated by feeding AVERAGE a range that includes the...

The IF function is evaluated first with the following logical test:
names=F6
This generates an array of TRUE / FALSE values, where TRUE corresponds to rows where the name matches the value in F6:
{TRUE;FALSE;FALSE...

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

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

In the example shown, the goal is to calculate the minimum difference of sales minus cost, but only when both values have been entered. If either value is blank, the result should be ignored. To confirm that both...

The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data.
The UNIQUE function, new in Excel 365, will return a unique list of values from a set...

At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX, and INDEX returns the value at that position in the Trip column. The hard work is done...

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

This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine...

When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains...

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