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:
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.
- 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.
- 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
- 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).