Work in Progress :: Please contact us to report errors, typos, etc.
L_REPELEM
Repeat each element of an array m times vertically and n times horizontally.
=L_REPELEM(array, m_vert, n_horiz)
Argument | Description | Example |
---|---|---|
array | An array or range of numbers or text | 2 |
m_vert | Number of times to repeat the elements vertically | 2 |
n_horiz | Number of times to repeat the elements horizontally | 2 |
Description
The L_REPARRAY function repeats (or stacks) the entire array, while L_REPELEM repeats each individual element of the array. This can be very useful in constructing certain types of matrices.
If n_horiz is blank, then m_vert will be used for n_horiz. For example, L_REPELEM(array,3) is the same as L_REPELEM(array,3,3).
One use for the combination of L_REPARRAY and L_REPELEM is for constructing an array defining all possible combinations of rows within two arrays (see L_COMBINATIONS).
Lambda Formula
This code for using L_REPELEM in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.
Code to Create Function via the Name Manager
Name: L_REPELEM
Comment: Repeat each element of an array m times vertically and n times horizontally.
Refers To:
=LAMBDA(array,m_vert,[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/repelem.html",
m_vert,IF(ISBLANK(m_vert),n_horiz,m_vert),
n_horiz,IF(ISBLANK(n_horiz),m_vert,n_horiz),
MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j,
INDEX(array,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
))
))
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Repeat the elements of an array m times vertically and n times horizontally */ L_REPELEM = LAMBDA(array,m_vert,[n_horiz], LET(doc,"https://www.vertex42.com/lambda/repelem.html", m_vert,IF(ISBLANK(m_vert),n_horiz,m_vert), n_horiz,IF(ISBLANK(n_horiz),m_vert,n_horiz), MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j, INDEX(array,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0)) )) ));
Named Function for Google Sheets
Name: L_REPELEM Description: Repeat each element of an array m times vertically and n times horizontally. Arguments: array, m_vert, n_horiz (see above for descriptions and example values) Function: LET(doc,"https://www.vertex42.com/lambda/repelem.html", m_vert,IF(ISBLANK(m_vert),n_horiz,m_vert), n_horiz,IF(ISBLANK(n_horiz),m_vert,n_horiz), MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j, INDEX(array,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0)) )) )
Warning
These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.
L_REPELEM Examples
Example 1
Starting with the vector {1;2;3;4}, repeat each element 3 times vertically to get {1;1;1;2;2;2;3;3;3;4;4;4}
Test: Copy and Paste this LET function into a cell =LET( array, {1;2;3;4}, m_vert, 3, n_horiz, 1, L_REPELEM(array,m_vert,n_horiz) ) Result: {1;1;1;2;2;2;3;3;3;4;4;4}
Example 2
Repeat each element of the square array {1,2;3,4} 2 times.
Test: Copy and Paste this LET function into a cell =LET( array, {1,2;3,4}, m_vert, 2, n_horiz, 2, L_REPELEM(array,m_vert,n_horiz) ) Result: {1,1,2,2;1,1,2,2;3,3,4,4;3,3,4,4},
References & Resources
Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.