---
title: "Creating and Editing Formulas"
description: "Formulas are one of the most important concepts in Lucanet xP&A. Formulas let you work with numbers, ranges, and other variables."
source_url: https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas
language: en
last_updated: 2023-08-16
---
# 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](https://en.wikipedia.org/wiki/Triangular_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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/if-statements-in-formulas.md) for more!
- `sample(3, 5)`: either the value 3 or the value 5 (discrete distribution)

## Creating Formulas

To create or edit a formula:

{% stepper %}
{% stepper-step %}
Make sure that the formula column is displayed. If necessary, click **Formula** in the toolbar.
{% /stepper-step %}
{% stepper-step %}
Do one of the following:
 - Double-click in the desired cell in the formula column.
 - Select the desired cell and press **F2**.
{% /stepper-step %}
{% stepper-step %}
Enter the formula (see [Elements in Formulas](#eleinform)).
{% /stepper-step %}
{% stepper-step %}
Press **Enter** to save the formula.

{% /stepper-step %}
{% /stepper %}
{% info-box %}
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.
{% /info-box %}

## 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-edit-variables/referencing-variables-from-linked-models.md) 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/variable-modifiers.md).

#### Functions

Functions that can be used in formulas.

For more information, see [Functions](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/functions.md).

#### Helper variables

The following helper variables are available for formulas:

- **lastActualDate**: a helper variable that returns the timestep of the [Last Actual Date setting](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/working-with-time/last-actual-date.md), if it is toggled on (see [Helper Variables](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/helper-variables.md))
- **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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/helper-variables.md).

#### 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/create-andedit-formulas/if-statements-in-formulas.md).

{% info-box %}
You can preview the formula in the top bar:

Preview a formula
{% /info-box %}

## 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:

{% stepper %}
{% stepper-step %}
Enter the total value on the total (or branch) item in the formula bar.

The **Select splashing** dropdown list appears:

Splashing options
{% /stepper-step %}
{% stepper-step %}
Select the type of splashing you want to apply.

An explanation of the splashing types can be found in the following sub-chapter.
{% /stepper-step %}
{% stepper-step %}
Press **Enter** to apply the splashing to the underlying values.
{% /stepper-step %}
{% /stepper %}

{% warning-box %}
- 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.
{% /warning-box %}

### 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:

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:

Example before 'Pro-Rata' is applied

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

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:

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:

Example for 'Repeat'