OFFSET(Reference, Rows, Columns, Height, Width)
where,
- is a reference cell or base cell of the offset,
- represents the number of cells up or down the reference cell,
- represents the number of cells left or right to the reference cell,
- is an optional value that represents the number of rows to be displayed as the output, and
- is an optional value that represents the number of columns to be displayed as the output.
OFFSET() displays a specified number of rows and columns from the reference cell or the base cell.
Description
OFFSET(Reference, Rows, Columns, Height, Width)
- OFFSET function is used to display the value of cell that is specified number of rows or columns away from the reference.
- Offset reference should be within the spreadsheet, else Calci displays #NULL error message.
- can be positive or negative. If is positive, it means move down from the reference. If is negative, it means move up from the reference.
- can be positive or negative. If is positive, it means move right from the reference. If is negative, it means move left from the reference.
- If or is omitted, Calci assumes it to be the same Height or Width as the reference.
- and should be > 1, else Calci displays #NULL error message.
Examples
Consider the following examples that demonstrate the use of OFFSET function:
| Fruit | Color | Quantity |
| Orange | Orange | 20 |
| Banana | Yellow | 30 |
| Apple | Red | 18 |
| Strawberry | Red | 25 |
=OFFSET(A2,2,2,1,1) : Returns the value in cell that is located two rows down(2) and
one row to the right(2) from A2. Displays 18 as the output. =OFFSET(B3,1,-1) : Returns the value in cell that is located one row down(1) and
one row to the left(-1) from B3. Displays Apple as the output.