Use the formula editor in models
The formula editor helps you to create and edit formulas easily, and protects 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 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 function icon . Simply click the button or type 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.
To use the formula editor in a model line:
-
Click on an existing model line or click + Add Model Line.
-
Click the function icon
to display the formula editor, or press the equals key (=) on your keyboard.
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, type 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.
-
A grey checkmark
appears if your formula is not valid.
-
To delete the formula, click the X left of the equation.
-
-
Click Done.
Functions
Use functions in the formula editor like you would in Excel:
Function | Usage |
---|---|
IF |
Returns a specified value only if a single exact condition has been met. Syntax: =IF(condition, {value_if_true}, {value_if_false}) |
MIN |
Calculates the minimum value of a range of cells. Syntax: =MIN(number1, [number2], ...) |
MAX |
Calculates the maximum value of a range of cells. Syntax: =MAX(number1, [number2], ...) |
SUMTOTAL |
Adds up, or sums together, the total amounts of a range of cells for a defined budget period. Syntax: =SUMTOTAL(Model line variable,[number1],[number2], ...) If you use variables, such as Operators ( + - / * ...), IF, MAX, MIN and ROUND, in a specific argument of a SUMTOTAL formula, that argument uses the monthly value of the model line in the calculation. |
SUM |
Adds up, or sums together, a range of cells at the month level. Syntax: =SUM(number1,[number2],...) |
ROUND |
Rounds a single number to the nearest specified value, usually to a whole number. Syntax: =ROUND(number, num_digits) |
In this topic