Function Reference: OFFSET
| Syntax | ||
| =OFFSET(reference,rows,cols,[height],[width]) | ||
| Purpose / Description | ||
| Returns a reference to one or more cells at a given or calculated offset from a known reference. The dimensions of the reference returned can be based on the starting reference, or changed to a specified or calculated set of dimensions. | ||
| Arguments | ||
| Name | Type | Description |
| reference | reference | A reference or nested expression that returns a reference to a known or derived starting point for determining the reference to be returned. |
| rows | number | A constant, reference or nested expression that returns the number of rows between the targeted range and the starting reference. Any decimal portion of the value is ignored. Positive values specify the number of rows below reference, negative values specify the number of rows above reference where the targeted range commences. |
| cols | number | A constant, reference or nested expression that returns the number of rows between the targeted range and the starting reference. Any decimal portion of the value is ignored. Positive values specify the number of columns to the right of reference, negative values specify the number of columns to the left of reference where the targeted range commences. |
| [height] | number | A optional constant, reference or nested expression that returns the number of rows to be included in the targeted reference. Any decimal portion of the value is ignored. Counting from the location specified by =OFFSET(rows,cols), positive values specify the number of rows counting down and negative values specify the number of rows, counting up to be included in the targeted range. Defaults to the count of rows in reference, if omitted. |
| [width] | number | A optional constant, reference or nested expression that returns the number of columns to be included in the targeted reference. Any decimal portion of the value is ignored. Counting from the location specified by =OFFSET(rows,cols), positive values specify the number of columns counting to the right and negative values specify the number of columns counting to the left to be included in the targeted range. Defaults to the count of columns in reference, if omitted. |
| Return Values | ||
| Type | Specific Value | Description |
| reference | The reference to the range whose location and dimensions (optionally) have been specified, relative to the starting reference. | |
| error | #VALUE! | If any of the values passed to rows, cols, height or width are not numbers or cannot be coerced to a number. |
| error | #REF! | If the location of the range is logically partially or wholly outside the bounds of the worksheet. If the value passed to height or width is the number value 0. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Advice: | Although it is a neat way to describe a reference to a range, we generally eschew the use of the OFFSET function, as it can dramatically impact model calculation performance. Everything that can be achieved using this function can be achieved with alternative non-volatile calculations, as demonstrated in our courses and online tutorials.. Using it for dynamic ranges in simple charts and validation lists in smaller sized workbooks is okay, but for larger scale projects we think it is a poor approach. | |
| Warning: | This function is volatile, and if used indiscriminately in larger models and analytical workbooks will dramatically impact model calculation speed. | |
| Examples: | Open / download sample workbook: OFFSET.xlsx | |