Documentation/Calc Functions/OFFSET

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

OFFSET

Category:

Spreadsheet

Summary:

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

Syntax:

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

Returns:

Returns the content of the cell(s) whose reference's position and dimension are modified.

Arguments:

Reference is the reference from which the function searches for the new reference.

Rows is the number of rows by which the reference was corrected up (negative value) or down. Use 0 to stay in the same row.

Columns is the number of columns by which the reference was corrected to the left (negative value) or to the right. Use 0 to stay in the same column

Height is the vertical height for an area that starts at the new reference position.

Width is the horizontal width for an area that starts at the new reference position.

Arguments Rows and Columns must not lead to zero or negative start row or column.Arguments Height and Width must not lead to zero or negative count of rows or columns.

  • If Row and/or Column leads to a negative or 0 start then the function returns a (#REF!) reference error.

Additional details:

  • If the Width or Height is included, the OFFSET function returns a range and thus must be entered as an array formula. If both the Width and Height are missing, a cell reference is returned.

Examples:

Formula Description Returns
=OFFSET(A1;2;2) where C3 contains 100 The function returns the value in cell C3 (A1 moved by two rows and two columns down). 100
=SUM(OFFSET(B2:C3;1;1)) where C3:D4 contains 1,2,3,4 The function returns a reference to B2:C3 moved down by 1 row and one column to the right (C3:D4). Further the function sums up all the values. 10
=OFFSET(B2:C3;-1;-1) where A1:B2 contains 1,-1,1,-1 The function returns a reference to B2:C3 moved up by 1 row and one column to the left (A1:B2). Further the function sums up all the values. 0
=OFFSET(B2:C3;0;0;3;4) passed as an array function The function returns a reference to B2:C3 resized to 3 rows and 4 columns (B2:E4). (Contents of B2:E4 array are displayed)
=SUM(OFFSET(A1;2;2;5;6)) where each cell in C3:H7 contains 1 The function determines the total of the area that starts in cell C3 and has a height of 5 rows and a width of 6 columns (area=C3:H7). 30
=OFFSET(B2,-2,-2,4,4) The function returns a reference error since a non-existant cell occurs in the range. #REF!

Related LibreOffice functions:

COLUMN

COLUMNS

ROW

ROWS

ODF standard:

Section 6.14.11, part 2

Equivalent Excel functions:

OFFSET