In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula:
=LARGE(B4:B13, 2)
will return the 2nd largest value in the range B4:B13 which, in the example above, is the...

This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in...

To calculate gas (MPG) based on odometer readings you can build a table with a few simple formulas. In the example shown, the formulas in E5 and F5 are:
=[@Mileage]-SUM(C4) // E5 calculate mileage
=[@Distance]/[@...

This formula uses what is called a "mixed reference" to create an "expanding range". A mixed reference is a reference that includes both absolute and relative parts.
In this case, the SUM formula refers to the range...

This formula uses the named range "key" (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(--(C7:G7=...

The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example
Sheet1:Sheet3!A1
Means: cell A1 from Sheet1 to Sheet3.
In the example shown:
=...

The challenge with lookup formulas that retrieve more than one match is managing duplicates (i.e. multiple matches). Lookup formulas like VLOOKUP and INDEX + MATCH can easily find the first match, but it's much harder...

In the example shown, we have monthly data for the years 2017 and 2018. The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed...

The core of this formula is COUNTIF, which returns zero if none of the substrings is found, and a positive number if at least one substring is found. The twist in this case is that we are giving COUNTIF more than one...

SUMPRODUCT accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array.
The...

A weighted average, also called a weighted mean, is an average where some values count more than others. In other words, some values have more "weight". We can calculate a weighted average by multiplying the values to...

In its simplest form, SMALL will return the "nth smallest" value in a range. For example:
=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
However, if you supply an array...

Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows a one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and...

The core of this formula is COUNTIF, which is configured to count three separate values using wildcards:
COUNTIF(B5,{"x*","y*","z*"}
The asterisk (*) is a wildcard for one or more...

At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.
To...