Creating and Editing Formulas
Last updated on 2025-11-11
Overview
Formulas are one of the most important concepts in Lucanet xP&A. Formulas let you work with numbers, ranges, and other variables.
Here are some simple examples:
25: constant value of 251 to 5: range from 1 to 5 (technical detail: symmetric triangle distribution)(0 to 1) * MyVariable: the product of a range and another variableif VariableA > 10 then 1 else 0: Write 1 if variableA is greater than 10, otherwise write 0. You can link multiple conditions with and and/or or. See if statements for more!sample(3, 5): either the value 3 or the value 5 (discrete distribution)
This article contains the following sections:
Creating Formulas
To create or edit a formula:
- Make sure that the formula column is displayed. If necessary, click Formula in the toolbar.
- Do one of the following:
- Double-click in the desired cell in the formula column.
- Select the desired cell and press F2.
- Enter the formula (see Elements in Formulas).
- Press Enter to save the formula.
If you are getting an error with a variable, you can hover over the formula error or the variable and xP&A will try to explain the cause of the error.
Elements in Formulas
Element
Description
Basic operations
The syntax for basic operations is the same as Excel: + - * / ^ > < =.
Not equal to
<> or !=
Reference variables
You can reference variables in formulas by typing the variable name and selecting it from the autocomplete dropdown. You can also reference variables in other models if they're linked.
Variable modifiers
When you reference a variable in a formula, there are two key ways to modify it - using Dimensions (if applicable), and Time.
For more information, see Variable Modifiers.
Functions
Functions that can be used in formulas.
For more information, see Functions.
Helper variables
The following helper variables are available for formulas:
- lastActualDate: a helper variable that returns the timestep of the Last Actual Date setting, if it is toggled on (see Helper Variables)
- blank: Blank values are treated like 0s in most formulas (e.g. blank + 7 = 0 + 7 = 7), except in instances involving a set of numbers (e.g. avg, min/max/median, count etc) where blanks will be excluded (e.g. avg(5,10,blank) = avg(5,10) = 7.5).
For more information, see Helper Variables.
If-statements
If you want a variable to have different values or formulas based on a condition, you can use if-statements. They always have to follow the structure if condition then X else Y.
For more information, see IF Statements.
You can preview the formula in the top bar:
Adding a Comment to a Formula
You can add comments to your formulas with a double slash (//). Everything between the double slash and the end of the line will be ignored by xP&A when calculating.
Distributing Values Using Splashing in a Formula
When creating a variable that is broken down by dimensions, you can enter a value at the total level to propagate the values below.
By default, when entering the value, the number is repeated on all the leaf levels (lowest possible dimension item of this dimension). Using the Splashing function, you are able to propagate dimension items with the following distribution types: Equal split, Pro-Rata, Repeat all leaves (which is the default), or Repeat.
To apply splashing to a formula:
Enter the total value on the total (or branch) item in the formula bar.
The Select splashing dropdown list appears:
Select the type of splashing you want to apply.
An explanation of the splashing types can be found in the following sub-chapter.
Press Enter to apply the splashing to the underlying values.
If you apply splashing on cells that already contain values, these values will be overwritten by the newly distributed values.
Available Splashing Types
The following splashing types are available.
You can use Equal split to evenly distribute a value to each level.
The total amount will be equal to the entered number. If you have multiple levels, the value will first be distributed between the higher levels and then proportionally across all the items below.
Example after entering 800 on the total level:
You can use Pro-Rata to distribute values across each level proportionally to their current value.
Example:
The following example shows dimension items that already have values:
After entering 800 on the total level and applying Pro-Rata splashing, the values are distributed as follows:
You can use Repeat al leaves to repeat the entered value on each leaf level. Repeat all leaves is the default setting for value distribution.
Example after entering 800 on the total level:
You can use Repeat to repeat the value on the 1st level, and then evenly distribute the value to the nesting levels.
Example after entering 800 on the total level:
