The first COLUMN function generates an array of 7 numbers like this:
{2,3,4,5,6,7,8}
The second COLUMN function generates an array with just one item like this:
{2}
which is then subtracted from the first array to...

At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result.
The arguments for OFFSET are provided as follows:
For reference we use the first cell in the data range, B5, entered...

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

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

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

At the core, uses SUMPRODUCT to sum values in a row that have been "filtered" using logic based on MOD. The key is this:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
This snippet of the formula uses the COLUMN function to...

The key to understanding this formula is to realize that the INDEX function can be used to return a reference to entire rows and entire columns.
To generate a reference to the "last n columns" in a table, we build a...

In Excel, you can't easily create formulas that skip columns following a certain pattern, because the references in the formula will automatically change to maintain the relationship between the original source cell...

Working from the inside out, the logical criteria used in this formula is:
--(data=90)
where data is the named range B4:D12. This generates a TRUE / FALSE result for every value in data, and the double negative...

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

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

The core of this formula is the MOD function. MOD takes a number and divisor, and returns the remainder after division, which makes it useful for formulas that need to do something every nth time.
In this case, the...

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

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

Working from the inside out, the logical criteria used in this formula is:
(data)<70
where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data, and the double negative...