Note:OFFSET
Description
OFFSET returns the contents of a range that is offset from a starting point in the spreadsheet.
Syntax
OFFSET ( reference, rows, columns [, height] [, width] )
|
Parameter |
Description |
|
reference |
A reference to a cell from which the offset reference is based. If you specify a range reference, #VALUE! is returned. |
|
Rows |
The number of rows from reference that represents the upper-left cell of the offset range. A positive number represents rows below the starting cell; a negative number represents rows above the starting cell. If rows places the upper-left cell of the offset range outside the spreadsheet boundary, #REF! is returned. |
|
Columns |
The number of columns from reference that represents the upper-left cell of the offset range. A positive number represents columns right of the starting cell; a negative number represents columns left of the starting cell. If columns places the upper-left cell of the offset range outside the spreadsheet boundary, #REF! is returned. |
|
height |
A positive number representing the number of rows to include in the offset range. Omitting this argument assumes a single row. |
|
width |
A positive number representing the number of columns to include in the offset range. Omitting this argument assumes a single column. |
Remarks
OFFSET does not change the current selection in the worksheet. Because it returns a reference, OFFSET can be used in any function that requires or uses a cell or range reference as an argument.
Examples
This function returns the contents of cell D4:
OFFSET(B1, 3, 2, 1, 1)
This function returns the sum of the values in the range E3:F5:
SUM(OFFSET(A1, 2, 4, 3, 2))
Note:
If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.