OFFSET

Name of Formula: 

OFFSET 

Definition of OFFSET:

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

Sample Usage

OFFSET(A2,3,4,2,2)

OFFSET(A2,1,1)

Syntax

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

• cell_reference - The starting point from which to count the offset rows and columns.

• offset_rows - The number of rows to shift by.

  • offset_rows must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

• offset_columns - The number of columns to shift by.

  • offset_columns must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

• height - [ OPTIONAL ] - The height of the range to return starting at the offset target.

  • height is ignored unless OFFSET is used in an array formula.

• width - [ OPTIONAL ] - The width of the range to return starting at the offset target.

  • width is ignored unless OFFSET is used in an array formula.

Notes

• If offset_rows or offset_columns is negative, it is possible for the offset target to to be outside the upper or left edge of the spreadsheet. If this occurs, the #REF! error will be returned.

• If OFFSET is used as an array formula, it is possible for the value returned by the array formula to overwrite part of the offset target, causing a circular reference. If this occurs, the #REF! error will be returned.

 

Step 1. To use the OFFSET Formula, start with the Excellentable you would like to edit.

Step 2. Then type the OFFSET formula into the cell you have chosen to display the outcome:

Step 3. Fill in the 5 values

 

By adding the values you would like to calculate, Excellentable generates the outcome: