Returns a range (or could be a single value) that is relative to a specified cell reference.
reference starting point. Must be a cell (or cell range).
rows the number of row to offset from the original reference. Negative value means above the current cell.
columns the number of columns to offset from the original reference. Negative value means left of the current cell.
height height of the range returned. 1 is a single row.
width width of range returned. 1 is a single column.
Since OFFSET returns a range, it can be used in conjunction with any other function that expects a range as a parameter. For example, SUM. If its used on its own, the top left most value from the returned range is displayed.
Examples
OFFSET(C3, 1, 2, 1, 1) |
Returns the value from E4 |
OFFSET(C3, 0, 0, 2, 2) |
Returns range C3..D4 in this case, simply displays the value from C3 |
SUM(OFFSET(C3, 0, 0, 2, 2)) |
Sums the range C3..D4 |
SUM(OFFSET(C3, 2, 0, 2, 2)) |
Sums the range C5..D6 |
SUM(OFFSET(C3, 0, -2, 2, 2)) |
Sums the range A3..B4 |
SUM(OFFSET(C3:C10, 0, 1, 1, 1)) |
Sums the range D3..D10 |
See also: