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

When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN...

The core of this formula is the ROUNDUP function. The ROUNDUP function works like the ROUND function except that when rounding, the ROUNDUP function will always round the numbers 1-9 up. In this formula, we use that...

At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.
When given a date, WEEKDAY simply...

Working from the inside out, the MID formula is used to extract the text in B5, one character at a time. The key is the ROW/INDIRECT piece:
ROW(INDIRECT("1:100"))
which spins up an array containing...

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings...

To get the row number, the data is compared to the max value, which generates an array of TRUE FALSE results. These are multiplied by the result of ROW (data) which generates and array of row numbers associated with...

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

Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to "1:", creating a text range like this: "1:3"
This text is passed into INDIRECT, which evaluates...

At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.
To find...

The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there no row number information in the data...

Note: this formula uses two named ranges: "names" refers to C4:C11, and "groups" refers to B4:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...

Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...

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

The core of this formula is based on another formula that calculates the "current row" in a range:
=ROW()-ROW(rng.firstcell)+1
In brief, we get the current row in the workbook, then subtract the first row number of...