Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Concept: Tips for Writing Formulas

Concept: Tips for Writing Formulas

Writing

  • Break the formula into small chunks and test results.
  • Use commas to separate terms. In certain browsers where commas are decimal points, use semicolons to separate terms.
  • 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?
  • 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. 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. 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. 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 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).