≡ ▼
```=L_CIRCSHIFT(array, n, dim)
```
ArgumentDescriptionExample
arrayAn array or range of numbers or text{"A1","B1";"A2";"B2";"A3","B3"}
nNumber of times to shift i to i+1.2
dimensionRows (dimension=1), Columns (dimension=2)1

## 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.

### Code to Create Function via the Name Manager

```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)
)
)
))
```

### Code for AFE Workbook Module (Excel Labs Add-in)

```/**
* 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)
)
)
));
```

### Named Function for Google Sheets

```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)
)
)
)
```
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.

### 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

Example
Shift the vector defined by SEQUENCE(5,1,1) = {1;2;3;4;5} twice so that the result is {4;5;1;2;3}.
```Test: Copy and Paste this LET function into a cell
=LET(
array, SEQUENCE(5,1,1),
n, 2,
L_CIRCSHIFT(array,n)
)
```

This example helps explain how the L_CIRCSHIFT works for a more general array. If we have an array consisting of 5 rows (any number of columns), then we can use CHOOSEROWS(array,{4;5;1;2;3}) to return the array with the rows in that particular order.

References & Resources