Search

OFFSET

Returns the source value from a specific number of periods before or after the current period. Using a positive offset value refers to later periods, a negative value refers to earlier periods. A zero value refers to the current period.

A fill value can be specified for when the offset result is outside the timescale.

Syntax

OFFSET(x,n,z) 

where:

  • x: Source line item to offset
  • n: Offset value
  • z: Fill value to use if the result is outside the time scale

Format

Input Format Output Format

x: Number, Boolean, date, time period, list, text

n: Number

z: Number

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: Line item: Date, text-formatted, numeric, Boolean, hard-coded data, or variable
  • n: Number: Numeric line item, property, or expression
  • z: Number: Numeric line item, property, or expression

Constraints

The function has the following constraints:

  • OFFSET only returns a single value, and so can apply to any data type.
  • The time range providing arguments to a line item must match the time range for the result line item. Any formula that conflicts with this rule will be rejected. For more information see the Search page.
  • A non-time series function, used as a sub-expression of this function, must use the same time range as the line item to which the formula is applied. Combining time series functions and non-time series functions with a time range could return an unexpected result or an error message. For more information see the Time Ranges and Time Series Functions page.

Excel equivalent

Back to Top

Example

The formula

OFFSET(x, 2, z)

returns the value in line item x from 2 time periods later than the current cell. If the time period to return is outside the model range, the value from line item z is used instead.

The formula

OFFSET(x, -2, z)

returns the value in line item x from 2 time periods prior to the current cell. If the time period to be read is outside the model range, the value from line item z is used instead.

The formula

OFFSET(x, d, z)

returns the value in line item x from the number of time periods in line item d. A negative value will reference a period prior to the current cell, a positive number will reference a period later than the current cell. If the time period to be read is outside the model date range, the value from line item z is used instead.

Back to Top

Similar functions