Explanation
In this example, the goal is to repeat a range of values.
Both functions work natively with two-dimensional ranges and can accept a single array of numeric index numbers.
The formulas below work in two steps:
Step 1 is based on the formulaexplained in detail here.
This is done with theSEQUENCE functionand theMOD functionhere:
This code creates a single array of repeating numbers.
TheCHOOSECOLS functionis designed to return specificcolumnsfrom a range.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
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.
CHOOSEROWS Function
The Excel CHOOSEROWS function returns specific rows from an array or range.
The rows to return are provided as numbers in separate arguments.
Each number corresponds to the numeric index of a row in the given array.
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.
Related videos
The SEQUENCE function
