Help & Support ↗ Lucanet Learning Space ↗
Getting Started
Global Functions
Administration
Extended Planning & Analysis (xP&A)
xP&A Video Tutorials
User Roles in Lucanet xP&A
Lucanet xP&A User Interface
All Steps at a Glance
Basic Concepts and Elements
Creating a Model
Integrating Data
Modeling Your Data
Creating and Editing Variables
Creating and Editing Formulas
Helper Variables
Variable Modifiers
IF Statements in Formulas
Functions for Formulas
Common Formulas
Troubleshooting
Values with Uncertainty
Working with Time
Using Dimensions
Using Currency and Currency Translation
Viewing and Visualizing Data
Working with Models
Using Comments and Descriptions
Comparison: xP&A vs. Excel Performance
Consolidation & Financial Planning
ESG Reporting
Disclosure Management
XBRL Tagger
Lease Accounting
Banking & Cash Management
Data Collection
Tax Compliance & Reporting
Glossary

Creating and Editing Formulas

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)

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:

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.

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:

1

Enter the total value on the total (or branch) item in the formula bar.

The Select splashing dropdown list appears:

Shows the formula bar and the Select spashing dropdown menu with its types Equal split, pro-rata, Repeat all leaves, Repeat
Splashing options
2

Select the type of splashing you want to apply.

An explanation of the splashing types can be found in the following sub-chapter.

3

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.
  • Your latest selected splashing type is saved for the next entry. E.g.if you selected Equal split, this type of splashing will be used for the next splashing used by this user.

Available Splashing Types

The following splashing types are available.

Equal split

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:

Shows a dimension breakdown with splashing "Equal split"
Example for 'Equal split'

Pro-Rata

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:

Shows a dimension breakdown without splashing "Pro-Rata"
Example before 'Pro-Rata' is applied

After entering 800 on the total level and applying Pro-Rata splashing, the values are distributed as follows:

Shows a dimension breakdown with splashing "Pro-Rata"
Example after 'Pro-Rata' is applied

Repeat all leaves

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:

Shows a dimension breakdown with splashing "Repeat all leaves"
Example for 'Repeat all leaves"

Repeat

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:

Shows a dimension breakdown with splashing "Repeat"
Example for 'Repeat'

Last updated on Aug 16, 2023

Table of Contents
Creating and Editing FormulasOverviewCreating FormulasElements in FormulasAdding a Comment to a FormulaDistributing Values Using Splashing in a Formula
Related Articles

Company

Legal Notice

© Copyright 2026 - Lucanet AG