Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Tips for Writing Formulas

Includes a few helpful suggestions for writing formulas.

Writing

  • Break the formula into small chunks and test results.

  • Verify over several months, especially when doing forecasts where the actuals will overwrite plan periods.

  • Check account permissions.

  • Don't forget that a formula typically includes a calculation and the conditions that must be met to apply the calculation. For example, a pay raise may be a defined as a simple 10% pay increase, but you also need to include other conditions in the formula. For example: Is the person a full-time employee?; Have they reached their anniversary date?; and so on.

  • Document your formulas. Explain to yourself and others how they work. 

  • Add comments to explain what's happening. Comments are allowed within a formula starting with a pound or hashtag symbol (#) and ending at the next carriage return. For example:

ACCT.Rent[time=this-12] # last year's rent
* 1.5                   # 50% increase

  • Capitalize special words. Operators are not case sensitive. Consider using upper case and adding spaces to make them stand out. For example:

IF (VersionMonth(this) = VersionMonth(ROW.HireDate) AND IsBlank(ROW.TransferIn), 1, 0)

  • Consider using indentation to help readability and add carriage returns to limit line length. For example: 

IFF (
      IsBlank(ROW.EndDate)
         AND IsBlank(ROW.TransferOut)
         OR (VersionMonth(this) < VersionMonth(ROW.EndDate),
      ROW.Allocated_BeginningHeadcount + ROW.NewHire-ROW.Termination, 0
     )

  • Use meaningful, descriptive account names.  Formulas drive the data that appears in many locations throughout your Adaptive Planning instance. Meaningful names help you easily recognize how the data was calculated.

  • Be consistent with underscores (or dashes) and upper/lower case in account names. 

  • Spaces are ignored when a formula is evaluated, but they help make formulas more readable, and using them will help others understand your formulas. They can be omitted, but using them is a best practice.

To enter a carriage return in the Formula bar, type ALT-Enter.

Performance

  • Avoid references to the top-level organization structure or use (-). 
  • Create an assumption account which naturally evaluates at the top level, then caches the results.

Other

  • Individual syntax errors display the formula in red when you try to save an invalid formula. 
  • Other error types (divide by zero, invalid values, etc.) only appear when the formula is evaluated. They may not appear in Divf or Iff functions. 
  • Formulas entered directly in a cell require an equal sign (=), but when you use the Formula Assistant, the equal sign is not required (it is automatically implied). 

  • Was this article helpful?