Documentation/Calc Functions/OFFSET

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Calc Functions Home | List of functions | Abbreviations

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, 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 Data ▸ Calculate ▸ Recalculate or pressing F9, or on any input event.
Contrary to Calc, Microsoft Excel accepts negative values for the Height and Width arguments even though it goes against their documentation and the OOXML specification (see ECMA-376-1:2016 section 18.17.7.245). This means potentially different results when the same file is opened in both applications. See tdf#157948.

Examples:

Data for examples
	A	B	C	D	E	F	G	H
1
2	123.4			2.7	3.6	4.8	2.9
3	1	3	LibreOffice	1	1	1	1	1
4	2	4		1	1	1	1	1
5				1	1	1	1	1
6				1	1	1	1	1

Formula Description Returns

=OFFSET(A1; 2; 2)

Determine the cell that is 2 rows below and 2 columns to the right of cell A1. The Height and Width arguments are omitted, so both assume the default values of 1. The OFFSET function therefore returns a reference to cell C3 and the formula returns the content of that cell.

LibreOffice

=OFFSET(B1; D2; E2; F2; G2)

OFFSET truncates the decimal numbers supplied as arguments to integer values (B1; 2; 3; 4; 2). The function determines the cell that is 2 rows below and 3 columns to the right of cell B1 (that is cell E3). It further determines that this is the top left cell of an area 4 rows high and 2 columns wide. The OFFSET function therefore returns a reference to the cell range E3:F6. Assuming a non-array formula (created using the ↵ Enter key), then this results in a #VALUE! error.

#VALUE!

=SUM(OFFSET(B1; D2; E2; F2; G2))

Similar to the previous example. The SUM function takes the resulting cell range of the OFFSET function (E3:F6) as argument, performing a sum on its numeric values.

8

{ =OFFSET(B1; 2; 3; 4; 2) }

introducing it with the combination of Control + ⇧ Shift + ↵ Enter keys.

As array formula, the resulting cell range (E3:F6) determines the area of cells that will occupy the array (4 rows by 2 columns). Each cell of the resulting array will display the corresponding value of the cell range provided by the OFFSET function. The "home" cell that contains the array formula will display the value of cell E3; the adjacent cell to the right (within the array) will display the value of cell F3; the adjacent cell below the formula cell (one row down, within the array) will display the value of cell E4; and so on.

1	1
1	1
1	1
1	1

=SUM(OFFSET(B2:C3; 1; -1))

The OFFSET function returns a reference to the cell range A3:B4 and the formula returns the sum of the values in the cells of that range.

10

=OFFSET(D4; -2; -3)

Starting from cell D4, travel 2 rows up and 3 columns to the left (to reference cell A2); then provide its value.

123.4

{ =OFFSET(B2:C3; 0; 0; 3; 4) }

as array formula.

Since the Rows and Columns arguments are both set to zero, the OFFSET function makes no change to the top left cell of the range. However the original range was two rows deep and two columns wide, while the updated range is 3 rows deep and 4 columns wide. The content of cell B2 appears in the formula cell, the content of cell B3 appears in the cell below the formula cell, the content of cell B4 appears in the next cell below, and so on.

		2.7	3.6
3	LibreOffice	1	1
4		1	1

=SUM(OFFSET(B1; 2; 2; 4; 5))

The OFFSET function returns a reference to the cell range D3:H6. The formula returns the sum of the values in the cells within that range.

20

{ =OFFSET(A1; 0; 0; ; 6) }

as array formula.

The Height argument may be omitted (defaulting to 1) while the Width argument is given a value. Return a reference to the cell range A1:F1. The formula results in the content of cell A1 appearing in the formula cell, the content of cell B1 appearing in the cell to the right of the formula cell, the content of cell C1 appearing in the next cell to the right, and so on.

See Description column

Related LibreOffice functions:

ODF standard:

Section 6.14.11, part 2

Related (or similar) Excel functions:

OFFSET