OFFSET
Use the OFFSET function to return values from past or future periods.
Syntax
OFFSET(Value to offset, Number of periods, Substitute value)
Argument | Data type | Description |
Value to offset (required) | Number, Boolean, date, time period, list, text | The value to return from a past or future period. |
Number of periods (required) | Number | The number of periods away to return Value to offset from. |
Substitute value (required) | Same as the Value to offset argument | The value to use if the Number of periods argument specifies a period that is outside of model calendar. |
Returns
The OFFSET function returns a result of the same format as the Value to offset and Substitute value arguments.
Arguments
The Value to offset argument can be a line item, property, or expression of any format.
The Number of periods argument can refer to a numeric line item, property, or expression. If you use a positive this argument, OFFSET returns a value from a future period. A negative value returns a value from a past period. A value of zero returns the value from the current period.
The Substitute value argument must be a line item, property, or expression of the same format as the Value to offset argument.
Constraints
The OFFSET function has these constraints:
- The result line item must be the same format as the Value to offset and Substitute value arguments.
- The result line item must use the same time range as the Value to offset and Substitute value arguments.
- If a calculation function provides the value for an argument in the OFFSET function, it should use the same time range as the result line item. If you do not use the same time range for arguments and the result line item, it can result in an Invalid formula error.
Excel equivalent
Examples
OFFSET(x, 2, z)
This formula returns the value in x from two time periods after the current cell. If the value to return is outside of the module’s time range, the formula instead returns the value of z.
OFFSET(x, -2, z)
This formula returns the value in x from two time periods before the current cell. If the value to return is outside of the module’s time range, the formula instead returns the value of z.
OFFSET(x, d, z)
This formula returns the value in x from future or past periods that the numeric value d specifies. If d is a positive value, the formula returns the value for x from a future period. If d is a negative value, the formula returns the value for x from a past period. If the value to return is outside of the module’s time range, the formula instead returns the value of z.