Last updated: January 4, 2021

This article explains how to use the formula editor when you work with models. For more information on creating a model, see the short video on Getting started with models. If you’re looking for a more in-depth case study on models and scenarios, watch Financial modeling and scenarios in Sage Intacct Budgeting and Planning.

The formula editor

The formula editor helps you to create and edit formulas easily, while protecting you from computational errors. You can use the formula editor to calculate values in assumptions and amounts in model lines. When you open the formula editor, all you need to do is choose from the assumptions you have available in your budget or from the model lines lines you defined, and use the symbols and functions available on the calculator. Your formulas can be edited at any time using the formula editor, and all totals and running balances are automatically recalculated as the formula changes.

You can access the formula editor from any field that you see the fx icon. Simply click on the icon or press the equals key (=) on your keyboard.

📝 If you’re adding a formula or numeric value to a fixed assumption, simply press the equals key (=) in the Value field to display the formula editor (as shown in step 3 below).

To use the formula editor in a model line:

1. Click on an existing model line or click on + Add Model Line. The following window is displayed:

2. Click the fx icon to display the formula editor, or press the equals key (=) on your keyboard.

3. From the formula editor window, you first see the assumptions and model lines available in your current model (the model that you’re working in, or “this model”). Below your current model, you can see labels of the assumptions and model lines from other models you have in your budget, if any exist.

The assumption labels appear first and then the model line labels in the order they appear in your model. If you are editing the formula of an existing assumption or model line, the remaining labels are in the order they appear in your model before and after your selection.

📝 If you can’t find the assumption or model line that you’re looking for, simply type in the name and options appear.

Click on the labels to create your formula. A green checkmark () appears to the right of your equation to show that your formula is valid. If the formula is not valid, the checkmark appears gray. If you want to delete the formula, click the X to the left of the equation to clear it.

4. Click Done when you’re finished.

Using functions

Use functions in the formula editor like you would in MS Excel:

  • IF returns a specified value only if a single exact condition has been met. The syntax is “=IF(condition, {value_if_true}, {value_if_false})”.

  • MIN calculates the minimum value of a range of cells. The syntax is “=MIN(number1, [number2], ...)”.

  • MAX calculates the maximum value of a range of cells. The syntax is “=MAX(number1, [number2], ...)”.

  • SUMTOTAL adds up, or sums together, the total amounts of a range of cells for a defined budget period. The syntax is “=SUMTOTAL(Model line variable,[number1],[number2], ...)

  • SUM adds up, or sums together, a range of cells at the month level. The syntax is “=SUM(number1,[number2],...)”.

  • ROUND rounds a single number to the nearest specified value, usually to a whole number. The syntax is “=ROUND(number,num_digits)”.

📝 Note that when you use the SUMTOTAL function, if you use variables (such as: Operators ( + - / * ...), IF, MAX, MIN and ROUND) in a specific argument of the formula, that argument uses the monthly value of the model line in the calculation.

Did this answer your question?