Creating and Editing Formulas

Last updated on 2025-03-03

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 25
  • 1 to 5 : range from 1 to 5 (technical detail: symmetric triangle distribution)
  • (0 to 1) * MyVariable : the product of a range and another variable
  • if 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:

  1. Make sure that the formula column is displayed. If necessary, click Formula in the toolbar.
  2. Do one of the following:
    • Double-click in the desired cell in the formula column.
    • Select the desired cell and press F2.
  3. Enter the formula (see Elements in Formulas).
  4. 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

The following elements can be used 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:

Shows the formula preview field in the top bar of a spreadsheet Preview a formula
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.