Forum Discussion
Copy Array N times with excel formula
- Jan 18, 2024
Borja710 My favorite method for repeating values in Excel for MS365 is the IF / SEQUENCE method. For example, to repeat the word "Test" 3 times in a 1 column x 3 row array, use the following formula:
=IF(SEQUENCE(3), "Test")
Basically, the IF function treats any numeric value in the logical_test argument as TRUE (with the exception of 0, which is treated as FALSE). Since SEQUENCE(3) returns {1;2;3}, the IF function reads this as {TRUE;TRUE;TRUE}, and since the [value_if_true] is "Test", it returns {"Test";"Test";"Test"} as the final result.
This method can also be used to repeat a one-dimensional range or array. For example, to repeat a single row of data 3 times, the same formula can be applied as follows:
=IF(SEQUENCE(3), A1:C1)
Likewise, to repeat a single column of data 3 times, the same formula can be used, but with the [columns] parameter instead:
=IF(SEQUENCE(, 3), A1:A3)
This can also be combined with either TOROW or TOCOL to output the results to a single row or column. For example, your X-axis points can be generated and output to a single row with the following formula:
=TOROW(IF(SEQUENCE(6), SEQUENCE(, 3, 0, 5)))
Likewise, the formula for the Y-axis points would be virtually the same, but with the rows and [columns] arguments reversed:
=TOROW(IF(SEQUENCE(, 3), SEQUENCE(6,, 0, 2)))
And with the necessary variables input directly on the worksheet, the final formula would look something like this (combined with the VSTACK function):
=VSTACK( TOROW(IF(SEQUENCE(E5+1), SEQUENCE(, E4+1, C4, F4))), TOROW(IF(SEQUENCE(, E4+1), SEQUENCE(E5+1,, C5, F5))) )
Repeating Arrays with IF / SEQUENCE
Please see the attached workbook, if desired...
Borja710 My favorite method for repeating values in Excel for MS365 is the IF / SEQUENCE method. For example, to repeat the word "Test" 3 times in a 1 column x 3 row array, use the following formula:
=IF(SEQUENCE(3), "Test")
Basically, the IF function treats any numeric value in the logical_test argument as TRUE (with the exception of 0, which is treated as FALSE). Since SEQUENCE(3) returns {1;2;3}, the IF function reads this as {TRUE;TRUE;TRUE}, and since the [value_if_true] is "Test", it returns {"Test";"Test";"Test"} as the final result.
This method can also be used to repeat a one-dimensional range or array. For example, to repeat a single row of data 3 times, the same formula can be applied as follows:
=IF(SEQUENCE(3), A1:C1)
Likewise, to repeat a single column of data 3 times, the same formula can be used, but with the [columns] parameter instead:
=IF(SEQUENCE(, 3), A1:A3)
This can also be combined with either TOROW or TOCOL to output the results to a single row or column. For example, your X-axis points can be generated and output to a single row with the following formula:
=TOROW(IF(SEQUENCE(6), SEQUENCE(, 3, 0, 5)))
Likewise, the formula for the Y-axis points would be virtually the same, but with the rows and [columns] arguments reversed:
=TOROW(IF(SEQUENCE(, 3), SEQUENCE(6,, 0, 2)))
And with the necessary variables input directly on the worksheet, the final formula would look something like this (combined with the VSTACK function):
=VSTACK(
TOROW(IF(SEQUENCE(E5+1), SEQUENCE(, E4+1, C4, F4))),
TOROW(IF(SEQUENCE(, E4+1), SEQUENCE(E5+1,, C5, F5)))
)
Repeating Arrays with IF / SEQUENCE
Please see the attached workbook, if desired...