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