Site icon ExcelNotes

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:

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

Exit mobile version