Concept: Tips for Writing Formulas
Improve Expected Results
- Break formulas into small chunks, save, and test results. Then add complexity a chunk at a time.
- Use commas to separate terms. In certain browsers where commas are equivalent to decimal points, use semicolons to separate terms.
- Verify over several months, especially when the actuals will overwrite plan periods.
Improve Readability and Understanding
- Add comments to formulas that explain what's happening. Put comments in between hashtags. Example: ACCT.Rent[time=this-12] * 1.5 # 50% increase to last year's rent #
- Capitalize special words. Formulas aren’t case sensitive so you can use caps to make terms stick out.
- Use indentation, spaces, and line breaks to improve readability. When you’re typing in the formula bar, press Alt-Enter to break a line.Example: Break down the formula into different lines.ACCT.Rent[time = this - 12]* 1.5 50% increase# 50% increase to last year's rent #
Improve Performance
- Test formulas with IF and DIV function. Once you validate that they're working, use IFF and DIVF to improve the performance.
- Avoid references to the top-level organization structure. Instead create an assumption account which always evaluates at the top level.