Resources
Help & Support ↗ Lucanet Learning Space ↗
Documentation
Getting Started
Global Functions
Lucanet Lume
Administration
Data Hub
Consolidation & Financial Planning
Lease Accounting
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
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
ESG Reporting
Disclosure Management
XBRL Tagger
Tax Compliance & Reporting
Banking & Cash Management
Data Collection
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 and other variables.

Here are some simple examples:

  • 25: constant value of 25
  • 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

Copying Formulas to Other Rows in Lucanet xP&A

You can use Fill Down in Lucanet xP&A to copy a formula to other variable rows below it. When doing so, references to the source variable automatically adjust to each destination variable. References to any other variable remain unchanged.

Unlike Fill Right, which copies across time periods, Fill Down copies between variable rows.

To copy a formula to other rows in Lucanet xP&A:

1

Locate the source variable's cell in the formula column.

2

Move the mouse pointer to the bottom edge of the formula cell. Lucanet xP&A displays a purple handle with the tooltip Drag down to fill rows below.

3

Drag the handle down across the destination rows and release the mouse button to copy the formula.

Shows a formula being copied to the variable rows below it by dragging the purple Fill Down handle in Lucanet xP&A.
Copying a formula to other variable rows with Fill Down

You can undo the changes with Ctrl+Z.

Eligible Rows and Availability

The following conditions apply to Fill Down in Lucanet xP&A:

  • The Fill Down handle is available when you can edit the source formula and at least one eligible variable row exists below it.
  • Fill Down works across regular formula-variable rows, including top-level variables that have dimension breakdowns.
  • Fill Down skips the following rows:
    • Group and section headers
    • Dimension-item sub-rows
    • Dividers
    • Variables from linked models
    • Non-formula variable types
  • Fill Down is unavailable in change proposals, in read-only spreadsheets, or where you cannot edit formula cells.
  • Fill Down does not copy directly from one individual dimension item to another, and it does not automatically shift references to other variables as relative cell references in MS Excel do.

How References Change When Copying Formulas to Other Rows

Fill Down in Lucanet xP&A changes references to the source variable so that each copied formula refers to its own destination variable. Time modifiers, such as previous month, are retained. References to other variables keep pointing to those variables; they do not shift to neighboring rows as relative cell references do in MS Excel.

The following examples show formulas copied from Revenue to Profit in Lucanet xP&A:

ExampleFormula in RevenueCopied formula in Profit
Self-reference: 5% monthly growthRevenue previous month * 1.05Profit previous month * 1.05
Self-reference and another variableif timeStep = 0 then 100 else Revenue previous month + Costsif timeStep = 0 then 100 else Profit previous month + Costs

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, Pro-Rata (only non-empty), 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 'Equal split', 'Pro-Rata', 'Pro-Rata (only non-empty)', '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 the Equal split splashing type 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 the Pro-Rata splashing type 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

Pro-Rata (only non-empty)

You can use the Pro-Rata (only non-empty) splashing type to distribute a value proportionally across dimension items that already contain a value, including 0. Empty items are skipped and remain empty. In contrast, regular Pro-Rata can also distribute values into previously empty cells.

  • Zero is different from empty. If all included values are zero, Pro-Rata (only non-empty) distributes the amount equally among the non-empty items. In a hierarchy, the splashing type skips branches whose underlying items are all empty, even if the branch total is zero.

  • If every underlying item is empty, Lucanet xP&A cancels the edit without distributing the value and displays a corresponding warning.

The following table compares the two Pro-Rata splashing types in Lucanet xP&A:

Existing valuesPro-RataPro-Rata (only non-empty)
Numeric values, including zeroIncludedIncluded
Empty cellsMay receive valuesSkipped; remain empty
All underlying items emptyDistributes the value equallyCancels the edit and displays a warning

Example:

The following example shows dimension items that already have values:

Shows 'New Variable (2)' in Lucanet xP&A with a total of 8: 'B 1' contains 0, 'B 2' contains 8, and 'B3' and 'B4' are empty.
Example before splashing is applied

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

Shows 'New Variable (2)' in Lucanet xP&A after applying 'Pro-Rata', with a total of 800: 'B 1' contains 0, 'B 2' contains 800, and the previously empty items 'B3' and 'B4' contain 0.
Example after 'Pro-Rata' is applied

With Pro-Rata (only non-empty), B 1 remains 0 and B 2 receives 800, while the previously empty items B3 and B4 remain empty:

Shows 'New Variable (2)' in Lucanet xP&A after applying 'Pro-Rata (only non-empty)', with a total of 800: 'B 1' contains 0, 'B 2' contains 800, and 'B3' and 'B4' remain empty.
Example after 'Pro-Rata (only non-empty)' is applied

Repeat all leaves

You can use the Repeat all leaves splashing type 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 the Repeat splashing type 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.

Page IDKB2135536

Last updated on Oct 27, 2026

Table of Contents
OverviewCreating Formulas in Lucanet xP&AElements in Formulas in Lucanet xP&ACopying Formulas to Other Rows in Lucanet xP&AHow References Change When Copying Formulas to Other RowsAdding a Comment to a Formula in Lucanet xP&ADistributing Values Using Splashing in a Formula in Lucanet xP&AAvailable Splashing TypesEqual splitPro-RataPro-Rata (only non-empty)Repeat all leavesRepeat
Related Articles

Company

Legal Notice

© Copyright 2026 - Lucanet AG