Search code examples
google-sheetsgoogle-sheets-formula

how to shift an array in google sheets using formulas

I trying to shift first hundred numbers back 10

=OFFSET(transpose(SEQUENCE(1, 100, 1, 1)), 10, 0) → gives me "Argument must be a range."

but if I break up the formula into

=transpose(SEQUENCE(1, 100, 1, 1)) then =OFFSET(A1:A100,10,0) then it works.

But i need it in one formula. how do I do it?

what I'm ultimately trying to do is to shift a column of data so that I can get year over year changes. like the shift function in pandas.

So I need to offset a given array and not regenerate it.

Solution

  • You can use:

    =LET(
   array, <your_array>,
   IFERROR(
     MAKEARRAY(ROWS(array), COLUMNS(array),
       LAMBDA(i, j,
         INDEX(
           array,
           i + <row_offset>,
           j + <col_offset>
         )
       )
     )
   )
 )

    Replace <your_array> with your array, <row_offset> with the row offset and <col_offset> with the column offset.