Help & Support ↗ Lucanet Learning Space ↗
Getting Started
Global Functions
Lucanet Lume
Data Hub
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 in Lucanet xP&A

Overview

Formulas are one of the most important concepts in Lucanet xP&A (Extended Planning & Analysis). 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 in Lucanet xP&A

To create or edit a formula in Lucanet xP&A:

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 in Lucanet xP&A

The following elements can be used in formulas in Lucanet xP&A:

ElementDescription
Basic operationsThe syntax for basic operations is the same as MS Excel: + - \ / ^ > < =*.
Not equal to<> or !=
Reference variablesYou can reference variables in formulas by typing the variable name and selecting it from the autocomplete drop-down list. You can also reference variables in other models if they're linked.
Variable modifiersWhen 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.
FunctionsFunctions that can be used in formulas.

For more information, see Functions.
Helper variablesThe 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-statementsIf 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 bar in Lucanet xP&A displaying the formula "= sum( Revenue this month )" for the "P&L with Multiple Entities and FX (Cloned)" row.
Preview a formula

Adding a Comment to a Formula in Lucanet xP&A

You can add comments to your formulas in Lucanet xP&A 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 in Lucanet xP&A

When creating a variable that is broken down by dimensions in Lucanet xP&A, 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 in Lucanet xP&A:

1

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

The Select splashing drop-down list appears:

Shows the formula bar in Lucanet xP&A with the 'Select splashing' drop-down list open, listing the options 'Equal split', 'Pro-Rata', 'Repeat all leaves', and '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 in Lucanet xP&A.

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 the "Cost Hierarchy" dimension breakdown in Lucanet xP&A after entering 800 on the total level with 'Equal split' splashing applied: 'Poll 1' 400, 'Poll 2' 200, 'Poll 4' 200, 'Poll 3' 200, and 'Poll 5' 400. Highlighted in purple is the total-level cell where 800 was entered.
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 the "Cost Hierarchy" dimension breakdown in Lucanet xP&A before 'Pro-Rata' splashing is applied, with existing values: 'Cost Hierarchy' 110, 'Poll 1' 90, 'Poll 2' 60, 'Poll 4' 60, 'Poll 3' 30, and 'Poll 5' 20.
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 the "Cost Hierarchy" dimension breakdown in Lucanet xP&A after entering 800 on the total level and applying 'Pro-Rata' splashing: 'Poll 1' 655, 'Poll 2' 436, 'Poll 4' 436, 'Poll 3' 218, and 'Poll 5' 145. Highlighted in purple is the total-level cell where 800 was entered.
Example after 'Pro-Rata' is applied

Repeat all leaves

You can use Repeat all 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 the "Cost Hierarchy" dimension breakdown in Lucanet xP&A after entering 800 on the total level with 'Repeat all leaves' splashing applied: 'Poll 1' 1,600, 'Poll 2' 800, 'Poll 4' 800, 'Poll 3' 800, and 'Poll 5' 800. Highlighted in purple is the total-level cell where 800 was entered.
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 the "Cost Hierarchy" dimension breakdown in Lucanet xP&A after entering 800 on the total level with 'Repeat' splashing applied: 'Poll 1' 800, 'Poll 2' 400, 'Poll 4' 400, 'Poll 3' 400, and 'Poll 5' 800. Highlighted in purple is the total-level cell where 800 was entered.
Example for 'Repeat'

This content was generated using AI and reviewed by Lucanet subject matter experts before publication.

Last updated on May 1, 2026

Table of Contents
OverviewCreating Formulas in Lucanet xP&AElements in Formulas in Lucanet xP&AAdding a Comment to a Formula in Lucanet xP&ADistributing Values Using Splashing in a Formula in Lucanet xP&AAvailable Splashing TypesEqual splitPro-RataRepeat all leavesRepeat
Related Articles

Company

Legal Notice

© Copyright 2026 - Lucanet AG