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

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

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

Working from the inside out, the logical criteria used in this formula is:
--(names=G4)
where names is the named range C4:E7. 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...

The ROW() function, when entered into a cell with no arguments with return the the row number of that cell. In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6....

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

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

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

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 first step is to construct a standard "A1" style reference using the column letter, by adding a "1" with concatenation:
B5&"1"
This results in a text string like "A1" which is passed into the...