---
url: /technical/formulas.md
description: >-
  MODLR has formulas in three places - on cubes, on card properties, and on
  workview set instructions. They share a syntax but do different jobs.
---

# Formulas

MODLR uses formulas in three distinct places. They share a syntax and a function library, which makes them look interchangeable, but each one produces something different and runs at a different moment. Knowing which kind you're writing is usually the whole of the problem.

| | [Cube formula](/technical/writing-a-cube-formula) | [Card formula](/technical/formulas-in-cards) | [Set instruction formula](/set-instructions/advanced-functions) |
| :--- | :--- | :--- | :--- |
| **Where it's written** | The Formula Editor, against a Cube | **Apply Formula**, against one component property | The Set Editor, reached from any surface that uses sets |
| **What it produces** | A cell's value | A property's value - text, colour, size, visibility | Which elements appear as rows or columns, and how they're labelled and styled |
| **When it runs** | When the cell is calculated | When the card renders | When the set is built |
| **What it applies to** | Every cell matching the formula's **Restrictions** | That one property | The set's rows or columns |
| **Stored in** | The model, as part of the Cube | The Card | Whatever holds the set - a Workview, a Card component, a Mapping, a Dashboard selectable, or an Excel `SetInstruction` |

## What they have in common

* Function names are written in capitals - `IF`, `LINK`, `ELEMENT`, `SPLIT`.
* A relative cube location is written in square brackets with double-quoted elements: `["Revenue","All Products"]`.
* [`ELEMENT`](/cube-functions/element) reads the current context, so a formula can respond to the cell, card or row it's being evaluated for.
* [`VARIABLE`](/technical/reserved-variables) reads a model variable or one of the built-in reserved variables.
* `&` concatenates.

The [Cube Functions](/cube-functions/) reference is the shared library - but shared is not the same as universal. A function's reference page states the contexts it runs in, under **Only Executes Under**.

### Functions that only run in some contexts

| Function | Runs under |
| :--- | :--- |
| [`RAND`](/cube-functions/rand), [`RANDBETWEEN`](/cube-functions/randbetween), the `SQL*` functions, [`CHILDREN`](/cube-functions/children), [`DESCENDANTS`](/cube-functions/descendants), [`CONTEXTDUMP`](/cube-functions/contextdump) | Cards only |
| [`NOW`](/cube-functions/now), [`TODAY`](/cube-functions/today), [`USERELEMENTACCESS`](/cube-functions/userelementaccess) | Cards, Workviews and Processes - **not** cube formulas |
| [`IRR`](/cube-functions/irr), [`XIRR`](/cube-functions/xirr) | Cube formulas only |

So a card property formula can pick a random number or read the current date; a cube formula can do neither. A cube formula can calculate an internal rate of return across a range of cells; a card formula uses [`CUBEIRR`](/cube-functions/cubeirr) instead.

### Relative locations are literal in a cube formula

A cube formula's relative location has to resolve to element names directly - it can't be assembled from a string expression. This is valid in a card or set instruction formula, but not in a cube formula:

```js
LINK("Profit and Loss", [ELEMENT("Time") & " YTD"])
```

Card and set instruction formulas are evaluated at render time against a known context, so they can build an element name as a string. A cube formula is compiled against the cube's structure, so the elements it references have to be nameable up front.

Where a cube formula genuinely needs an element it can't name literally, that's the job of [`LINKBY`](/technical/linkby-references) with a [Mapping](/technical/mappings) - the mapping resolves the element the formula can't determine on its own.

## Checking a formula

Whichever kind you're writing, [Testing and explaining formulas](/technical/testing-and-explaining-formulas) covers how to confirm it does what you meant - validating and testing before saving, and tracing a value back through the formulas that produced it.

## Related

* [Writing a Cube Formula](/technical/writing-a-cube-formula) - cube formulas in full
* [Opening the Formula Editor](/technical/opening-the-formula-editor) - the editor and its syntax rules
* [Formulas in Cards](/technical/formulas-in-cards) - driving card properties with formulas
* [Set Instructions](/set-instructions/) - the workview set instruction reference
* [Reserved Variables](/technical/reserved-variables) - the built-in variables formulas can read
* [Cube Functions](/cube-functions/) - the function library