L_CIRCSHIFT

=L_CIRCSHIFT(array, n, dim)

Description

The L_CIRCSHIFT function allows you to shift the rows of an array a number of times. The circular pattern refers to the first row being shifted to the second, the second to the third and so on, with the last row being shifted to the first row.

Using dimension=1 (the default) shifts the rows, and dimension=2 shifts the columns.

The n parameter can be negative to shift the array in reverse (causing the first row to become the last, the second to be the first and so on).

This function was designed to be similar to the circshift function in MATLAB and the roll function in NumPy.

Lambda Formula

This code for using L_CIRCSHIFT 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.

Name: L_CIRCSHIFT
Comment: Shift the rows (dim=1) or columns (dim=2) of an array circularly n times.
Refers To:

=LAMBDA(array,n,[dimension],
LET(doc,"https://www.vertex42.com/lambda/circshift.html",
    rows,ROWS(array),cols,COLUMNS(array),
    by_col,dimension=2,
    dim,IF(by_col,cols,rows),
    m,IF(by_col,1+MOD(INT(n)-1,cols),1+MOD(INT(n)-1,rows)),
    indices,VSTACK(SEQUENCE(m,1,dim-m+1),SEQUENCE(dim-m,1)),
    IF(dim=m,array,
        IF(by_col,
            CHOOSECOLS(array,indices),
            CHOOSEROWS(array,indices)
        )
    )
))

/**
* Shift the rows (dim=1) or columns (dim=2) of an array circularly n times.
*/
L_CIRCSHIFT = LAMBDA(array,n,[dimension],
LET(doc,"https://www.vertex42.com/lambda/circshift.html",
    rows,ROWS(array),cols,COLUMNS(array),
    by_col,dimension=2,
    dim,IF(by_col,cols,rows),
    m,IF(by_col,1+MOD(INT(n)-1,cols),1+MOD(INT(n)-1,rows)),
    indices,VSTACK(SEQUENCE(m,1,dim-m+1),SEQUENCE(dim-m,1)),
    IF(dim=m,array,
        IF(by_col,
            CHOOSECOLS(array,indices),
            CHOOSEROWS(array,indices)
        )
    )
));

Name: L_CIRCSHIFT
Description: Shift the rows (dim=1) or columns (dim=2) of an array circularly n times.
Arguments: array, n, dimension (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/circshift.html",
    rows,ROWS(array),cols,COLUMNS(array),
    by_col,dimension=2,
    dim,IF(by_col,cols,rows),
    m,IF(by_col,1+MOD(INT(n)-1,cols),1+MOD(INT(n)-1,rows)),
    indices,VSTACK(SEQUENCE(m,1,dim-m+1),SEQUENCE(dim-m,1)),
    IF(dim=m,array,
        IF(by_col,
            CHOOSECOLS(array,indices),
            CHOOSEROWS(array,indices)
        )
    )
)

How it Works

In this function, the user could choose to shift an array any number of times, or even to shift backwards by entering a negative number for n. To handle the case where |n| is greater than the number of rows, we calculate m as 1+MOD(n-1,rows) to use in assembling the new indice sequence.

For example, if the number of rows is 5, and n=7, then the result should be the rows shifted only m=2 times. The new sequence of indices is assembled by stacking {4;5} and {1;2;3} to form the vector {4;5;1;2;3}.

L_CIRCSHIFT Examples

Test: Copy and Paste this LET function into a cell
=LET(
    array, SEQUENCE(5,1,1),
    n, 2,
    L_CIRCSHIFT(array,n)
)