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

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

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

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

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

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

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

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

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

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

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