OFFSET function in Excel

Function OFFSET (OFFSET) in Excel returns the cell or range of cells that is the specified distance from the specified cell or range of cells.

  1. Function OFFSET (OFFSET) in the figure below returns the cell that is 3 rows below and 2 columns to the right of the cell A2… Function OFFSET (OFFSET) returns one cell because the specified height and width are 1.

    =OFFSET(A2,3,2,1,1)

    =СМЕЩ(A2;3;2;1;1)

    Result:

  2. Function OFFSET (OFFSET) in the following figure returns a 1×2 range that is 5 rows below and 1 column to the right of the cell A2… Function SUM (SUM) calculates the sum of the values ​​in this range.

    =SUM(OFFSET(A2,5,1,1,2))

    =СУММ(СМЕЩ(A2;5;1;1;2))

    Result:

Note: To return cell values ​​(without calculating the sum), select a range of the same size before pasting the function OFFSET (OFFSET). If you want the formula to return the cell or range of cells above and to the left of the origin point, enter negative numbers as the second and third arguments.

Leave a Reply