1. Calculation functions
  2. All Functions
  3. Time and Date Functions
  4. OFFSET

OFFSET returns a value from a period before or after the current period. 

You could use OFFSET to compare how today's regional sales compare to yesterday's. 

Syntax

OFFSET(Value to offset, Offset amount, Substitute value)

Arguments

ArgumentData TypeDescription
Value to offsetNumber, 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 is 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 format as the Value to offset amount. 

Syntax example

OFFSET(Value to offset, 2, 0)


JanFebMarAprilMayJune
Value to
offset

  3,000

4,000

4,500

 3,700

3,500

4,100
OFFSET
(Value to offset, 2, 0)
  4,5003,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.
  • 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

Related Anaplan functions

Detailed example


JanFebMarAprilMayJune
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,
Substitute value)
 2,8003,0004,0004,5003,7003,500