This problem can be solved in several ways, as explained below.
The result from FILTER is returned to MAX, which returns the maximum value as a final result.
This array is returned directly to the FILTER function as theincludeargument.
Thecolumnsargument is calculated by dividing the number of columns byn.
Bothstartandstepare assigned the valuen.
Both formulas will return the max values for nth columns for all rows at once.
The MOD function returns the remainder for each column number divided byn.
These values go into the IF function as the logical test.
Other values become FALSE.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference.
CHOOSECOLS Function
The Excel CHOOSECOLS function returns specific columns from an array or range.
The columns to return are provided as numbers in separate arguments.
Each number corresponds to the numeric index of a column in the given array.
COLUMN Function
The Excel COLUMN function returns the column number for a reference.
For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.
When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
MOD Function
The Excel MOD function returns the remainder of two numbers after division.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
