When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is...

TEXTJOIN is a new function in available in Office 365 and Excel 2019.
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...

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

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

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

The named range "sheetnames" is created with this code:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like...

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

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 core of this formula is the ADDRESS function, which is used to return a cell address based on a given row and column. Unfortunately, the formula gets somewhat complicated because we need to use ADDRESS twice: once...

Working from the inside out, the MID function is used to cast the string into an array of individual letters:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
In this part of the formula, MID, ROW, INDIRECT, and...

In Excel, you can't easily copy formulas that need to skip rows or columns following a certain pattern, because the references in the formula will automatically change following the relationship between the original...

The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the first row and the first column used by the named range data (B5:D14).
To get the first row used, we...

At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a...

This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn't work with non-numeric values. As a result, a large part of the formula simply...

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