Skip to main content

# 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?

• 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 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?