Circular Reference with OFFSET function

ryan_kohn
ryan_kohn
in Modeling

I'm getting a circular reference while using the OFFSET function, but I don't understand why.

 

(1) IF offset_value = -12 THEN OFFSET(Gross Revenue, -12, 0) ELSE OFFSET(Gross Revenue, -11, 0)

vs.

(2) OFFSET(Gross Revenue, offset_value, 0)

 

Dimension is Months and 'offset_value' always equals either -12 or -11 based on a formula. Why does (1) work but (2) is a circular reference?

 

Sample below:

Offset - Circular Reference.PNG

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓
    You could set the offset value to cause PY Gross to look at itself even though you know it can never be. Theoretically it could at some point and the system wont allow a user to create a circular reference. With a constant in the formula that can never happen.
    Hope that helps.

Answers

Categories