OFFSET returns a value from a period before or after the current period.
OFFSET
You could use OFFSET to compare how today's regional sales compare to yesterday's.
OFFSET(Value to offset, Offset amount, Substitute value)
Arguments
| Argument | Data Type | Description |
| Value to offset | Number, Boolean, date, time period, list, or text | Value to replace with a value from a different time period. |
| Offset amount | Number | Number of periods forward from which to retrieve a value. Positive values refer to future periods, negative to past periods, and zero the current period. |
| Substitute value | Same as Value to offset | Value to apply if the Offset amount specifies a period outside of the model's time range. |
The OFFSET function returns a value of the same data type as the Value to offset argument.
Syntax example
OFFSET(Value to offset, 2, 0)
| Jan | Feb | Mar | April | May | June | |
| Value to offset | 3,000 | 4,000 | 4,500 | 3,700 | 3,500 | 4,100 |
OFFSET | 4,500 | 3,700 | 3,500 | 4,100 | 0 | 0 |
Additional Information
OFFSET is the same as LEAD in the NONSTRICT mode.
Constraints
The Value to offset argument and result line item must share the same time range. Any formula that conflicts with this rule is rejected. For more information see the Time and Date Functions page.
Calculation engine functionality differences
In Polaris, a value of NaN (Not a Number) for the Offset amount argument returns the Substitute value argument.
In the Classic Engine, a value of NaN is equivalent to 0.
In the Classic Engine, you can use the OFFSET function only with a time dimension. In Polaris, you can use OFFSET with any dimension except Versions.
Excel equivalent
Examples
| Jan | Feb | Mar | April | May | June | |
| Value to offset | 3,000 | 4,000 | 4,500 | 3,700 | 3,500 | 4,100 |
| Substitute value | 2,800 | 3,800 | 3,200 | 3,500 | 3,300 | 3,900 |
OFFSET(Value to offset, -1, | 2,800 | 3,000 | 4,000 | 4,500 | 3,700 | 3,500 |
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.