---
title: "About formula expressions"
slug: "formula-expressions"
description: "Learn about formula expressions used to calculate the values in calculated columns."
updated: 2022-12-12T22:39:45Z
published: 2022-12-12T22:39:45Z
canonical: "docs.zilliant.com/formula-expressions"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.zilliant.com/llms.txt
> Use this file to discover all available pages before exploring further.

# About formula expressions

Use formula expressions to calculate the values of calculated columns. You can use any combination of:

- Basic mathematical operators ( **+**, **-**,  **/**)
- Common SQL functions (such as **Round ( )** )
- SQL language expressions (such as **CASE**)
- Any primary or related column name (as a variable).

For example, if you want to add a **New Cost Change** calculated column, you might specify the following formula expression:

```
CASE
            WHEN [Current Cost] is null
            or [Current Cost] = 0 THEN 0
            ELSE ([New Cost] - [Current Cost]) / [Current Cost]
END
```

where **Current Cost** and **New Cost** are columns in the same worksheet and their values are variables in the expression.

Following is a complete list of tested and supported SQL functions and language expressions.

#### Supported SQL language expressions

CASE

#### Supported SQL functions

- AVG()
- CAST()
- CEILING()
- COALESCE()
- CONVERT()
- COUNT()
- COUNT(DISTINCT)
- DATEDIFF()
- DATEADD()
- FORMAT()
- GETDATE()
- ISNULL()
- MAX()
- NULLIF()
- ROUND()
- SUM()

          Note

          

If you want to use a function or a language expression that isn't in this list, contact your Zilliant representative.

### Different field data types to use in the Column definition

The **Column settings** dialog has a **Fields** list that contains every column from the primary and related data sources in that worksheet. Each column listed has a symbol next to its label. These symbols indicate the value type of each column, which is the conceptual type of data contained in the field. You can use these symbols to better understand whether a specific field might be useful in the **Column Definition** formula for a calculated field. ![Field data types.png](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/Field%20data%20types.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

          Tip

          

These symbols are also useful when defining formula expressions in the **Update Definition** of the **Update Column** dialog when updating worksheet data. For more information, refer to [Update worksheet data](/v1/docs/update-worksheet-data).

**List of symbols**

- String
- Integer Number Percentage
- Currency
- Date
- DateTime
- Boolean
- Calculated

![text_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/text_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

String

![topic2_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/topic2_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

Integer, Number, Percentage

![currency_sign_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/currency_sign_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

Currency

![date_input_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/date_input_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

Date

![clock_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/clock_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

DateTime

![record_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/record_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

Boolean

![advanced_function_60](https://cdn.document360.io/24f960e8-3c1c-4c18-a2c6-83b6ae4aab84/Images/Documentation/advanced_function_60.png?sv=2026-02-06&spr=https&st=2026-11-04T17%3A30%3A16Z&se=2026-11-04T18%3A30%3A16Z&sr=c&sp=r&sig=E4RwHQffRlR2QscsIcp1l%2ByFSoJnHSxnRtcgOpRUlSE%3D)

Calculated