Search

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.

Similar functions