Documentation/Calc Functions/OFFSET

Function name:
OFFSET

Category:
Spreadsheet

Summary:
Determines a modified reference, given an initial starting point, specified row/column offsets, and specified height/width dimensions.

Syntax:
OFFSET(Reference; Rows; Columns[; Height[; Width]])

Returns:
Returns a modified reference to a cell or range of cells, constructed from the given arguments.

Arguments:
Reference is a cell reference, a reference to a cell range, or the name of a named range or database range. It provides the starting point for OFFSET to construct a modified reference based on the values of other arguments.

Rows is an integer value, or a reference to a cell containing an integer value, that specifies the number of rows to be offset from the starting reference. A negative value offsets up, a positive value offsets down, and 0 uses the same row.

Columns is an integer value, or a reference to a cell containing an integer value, that specifies the number of columns to be offset from the starting reference. A negative value offsets left, a positive value offsets right, and 0 uses the same column.

Height is a positive integer value, or a reference to a cell containing such a value, that specifies the total number of rows required in the modified reference. If omitted, a default value of 1 is applied.

Width is a positive integer value, or a reference to a cell containing such a value, that specifies the total number of columns required in the modified reference. If omitted, a default value of 1 is applied.


 * If Reference comprises multiple cell ranges (areas), then OFFSET reports a parameter list error (Err:504).


 * If any of Rows, Columns, Height, or Width is non-numeric, then OFFSET returns a #VALUE! error.
 * Non-integer values of Rows, Columns, Height, or Width are truncated to integers.


 * If, after any truncation, either Height or Width is less than 1, then OFFSET reports an invalid argument error (Err:502).


 * If the modified reference would contain one or more cells that lie outside the maximum sheet dimensions (1,048,576 rows by 1,024 columns), then OFFSET reports an invalid argument error (Err:502).


 * If Reference is a reference to a single cell, with both Height and Width omitted or set to 1, then OFFSET returns a single cell reference. Otherwise, OFFSET returns a reference to a cell range.

Additional details:

 * OFFSET is a volatile function. This means that Calc will update the reference returned by OFFSET when any cell is updated by selecting or pressing F9, or on any input event.

Related LibreOffice functions:
COLUMN

COLUMNS

ROW

ROWS

ODF standard:
Section 6.14.11, part 2

Equivalent Excel functions:
OFFSET