How to Use OFFSET Function

The OFFSET function returns a cell or data range that is specified by the number of rows and columns relative to another cell or data range.

Formula:

= OFFSET(reference, rows, cols, [height], [width])

Explanation:

  • Reference is required, the reference to be based on, referring a cell or data range.
  • Rows is required, the number of rows up or down (positive number means below the starting reference and negative number means above the starting reference).
  • Cols is required, the number of columns left or right (positive means to the right of the starting reference and negative means to the left of the starting reference).
  • Height is optional, in number of rows, the reference you want to be returned.
  • Width is optional, in number of columns, the reference you want to be returned.

Example 1:

=OFFSET(B2,2,2)

To look for the value 2 rows below B2, which is B4, and then 2 columns right of B4, which is D4. The value is 86632.

Example 2:

=SUM(OFFSET(B2:B3,2,1,2,1))

– To look for the data range 2 rows below B2:B3, which is B4:B5, then 1 column right, which is C4:C5.

– Height and Width: 2 rows and 1 column, which C4:C5.

– Count SUM of C4:C5, ($56,632+$67,321)= 123953

Example 3:

=SUM(OFFSET(B2:B3,2,1,2,2))

– To look for the data range 2 rows below B2:B3, which is B4:B5, then 1 column right, which is C4:C5.

– Height and Width: 2 rows and 2 columns from C4:C5 which is C4:D5.

– Count SUM of C4:C5, ($56,632+$67,321+ $86,632+$61,877)= 272462

Download: OFFSET Function

Leave a Reply