Formula columns - Retailgrid Documentation

Formula Columns allow you to calculate values dynamically based on other columns. They are essential for pricing, analytics, and decision-making workflows.

Formula column shown in a grid with calculated margin values

1. What is a formula column?

A Formula Column automatically computes values using:

Other columns (e.g. {Price}, {Unit Cost})
Functions (e.g. SUM, IF, ROUND)
Operators (+, -, *, /)

Unlike standard columns, values are not manually entered - they are always calculated.

2. How to create a formula column

Step 1: Add column

Click Columns → Add Column

Step 2: Select type

Select Formula

Step 3: Configure column

1. Name

Give a clear business name:

Margin %
Revenue
Price Index

2. Display format

Select how results are shown:

Format	Example
Number	123
Currency	€123.45
Percentage	25%
Text	”High”

3. Build formula

You have three ways:

3. Build formulas

3.1 Manual input

Type directly:

({Price} - {Unit Cost}) / {Price}

3.2 Insert column references

Click Insert column reference
Select fields from the list

Example:

{Price} * {Quantity}

3.3 Insert functions

Click Insert function
Select from the list

Example:

ROUND({Price}, 2)

Math and numeric functions

`ROUND(value, digits)`

Rounds a number to a specified number of decimals. Example:

ROUND({Price}, 2)

`TRUNC(value, digits)`

Truncates a number without rounding. Example:

TRUNC({Price}, 0)

`PRODUCT(value1, value2, ...)`

Multiplies values. Example:

PRODUCT({Price}, {Quantity})

`POWER(base, exponent)`

Raises a number to a power. Example:

POWER({Price}, 2)

`RAND()`

Generates a random number between 0 and 1. Example:

RAND()

Aggregation functions

`SUM(range)`

Returns sum of values.

SUM({Price})

`SUMIF(range, condition)`

Conditional sum.

SUMIF({Sales}, {Category} = "Electronics")

`MIN(range)`

Returns smallest value.

MIN({Price})

`MAX(range)`

Returns largest value.

MAX({Price})

`AVERAGE(range)`

Returns average.

AVERAGE({Price})

`MEDIAN(range)`

Returns median value.

MEDIAN({Price})

Counting functions

`COUNT(range)`

Counts numeric values.

COUNT({Price})

`COUNTA(range)`

Counts non-empty values.

COUNTA({Product Name})

`COUNTBLANK(range)`

Counts empty cells.

COUNTBLANK({Price})

`COUNTIF(range, condition)`

Counts values matching condition.

COUNTIF({Category}, "Electronics")

Logical functions

`IF(condition, value_if_true, value_if_false)`

Conditional logic.

IF({Price} > 100, "High", "Low")

String outputs from IF() pair well with Conditional color highlighting. Classify a row in a formula column, then map each classification to a color in the column header menu.

Text functions

`CONCAT(value1, value2, ...)`

Joins text values.

CONCAT({Brand}, " ", {Product Name})

Date and time functions

`NOW()`

Returns current date and time.

NOW()

`TODAY()`

Returns current date.

TODAY()

Operators (core syntax)

Arithmetic

+   addition
-   subtraction
*   multiplication
/   division

Comparison

=   equal
!=  not equal
>   greater than
<   less than
>=  greater or equal
<=  less or equal

4. Use AI to generate formulas

Step 1: Enable AI

Toggle AI ON

Step 2: Describe logic

Example inputs:

“Margin”
“Revenue = price times quantity”
“Discount percent”

Step 3: Generate

Click Generate by AI

The system will:

Create the formula
Validate the syntax

5. Validation and feedback

After writing a formula:

Formula is valid - ready to use.
Errors - fix syntax or references.

Common issues:

Missing {} around columns
Invalid function usage
Division by zero (logic issue)

6. Key concepts

6.1 Row-level calculation

Formulas work per row. Example:

{Price} * {Quantity}

Calculates revenue per product.

6.2 Column references

Always use {}. Correct:

{Price}

Wrong:

Price

6.3 Functions

Used for:

Aggregation
Logic
Transformation

Example:

IF({Price} > 100, "High", "Low")

7. Common use cases

Pricing

Margin %

({Price} - {Unit Cost}) / {Price}

Price Gap vs Competitor

({Price} - {Competitor Price}) / {Competitor Price}

Revenue

Revenue

{Price} * {Quantity}

Classification

Price Tier

IF({Price} > 100, "Premium", "Standard")

Promotions

Discount %

({Original Price} - {Price}) / {Original Price}

8. Best practices

Naming

Use business terms, not technical ones
Prefer:
- Gross Margin %
- Net Revenue

Simplicity

Avoid overly complex formulas
Break into multiple columns if needed

Reusability

Build intermediate columns:
- Margin
- Revenue
Then reuse in other formulas

Performance

Avoid heavy nested formulas on large datasets
Prefer simpler expressions

9. Limitations

Some column types do not support aggregation
Text fields cannot be used in numeric calculations
No multi-row / window functions (yet)

10. Recommended workflow

Ensure base data exists (Price, Cost, etc.)
Create core metrics:
- Revenue
- Margin
Add derived insights:
- Classification
- Flags
Refine with AI or manual tuning

11. Troubleshooting

Issue	Fix
Formula error	Check syntax
Empty results	Check null values
Wrong numbers	Validate input columns
Not updating	Check column references

​1. What is a formula column?

​2. How to create a formula column

​Step 1: Add column

​Step 2: Select type

​Step 3: Configure column

​1. Name

​2. Display format

​3. Build formula

​3. Build formulas

​3.1 Manual input

​3.2 Insert column references

​3.3 Insert functions

​Math and numeric functions

​ROUND(value, digits)

​TRUNC(value, digits)

​PRODUCT(value1, value2, ...)

​POWER(base, exponent)

​RAND()

​Aggregation functions

​SUM(range)

​SUMIF(range, condition)

​MIN(range)

​MAX(range)

​AVERAGE(range)

​MEDIAN(range)

​Counting functions

​COUNT(range)

​COUNTA(range)

​COUNTBLANK(range)

​COUNTIF(range, condition)

​Logical functions

​IF(condition, value_if_true, value_if_false)

​Text functions

​CONCAT(value1, value2, ...)

​Date and time functions

​NOW()

​TODAY()

​Operators (core syntax)

​Arithmetic

​Comparison

​4. Use AI to generate formulas

​Step 1: Enable AI

​Step 2: Describe logic

​Step 3: Generate

​5. Validation and feedback

​6. Key concepts

​6.1 Row-level calculation

​6.2 Column references

​6.3 Functions

​7. Common use cases

​Pricing

​Revenue

​Classification

​Promotions

​8. Best practices

​Naming

​Simplicity

​Reusability

​Performance

​9. Limitations

​10. Recommended workflow

​11. Troubleshooting

1. What is a formula column?

2. How to create a formula column

Step 1: Add column

Step 2: Select type

Step 3: Configure column

1. Name

2. Display format

3. Build formula

3. Build formulas

3.1 Manual input

3.2 Insert column references

3.3 Insert functions

Math and numeric functions

`ROUND(value, digits)`

`TRUNC(value, digits)`

`PRODUCT(value1, value2, ...)`

`POWER(base, exponent)`

`RAND()`

Aggregation functions

`SUM(range)`

`SUMIF(range, condition)`

`MIN(range)`

`MAX(range)`

`AVERAGE(range)`

`MEDIAN(range)`

Counting functions

`COUNT(range)`

`COUNTA(range)`

`COUNTBLANK(range)`

`COUNTIF(range, condition)`

Logical functions

`IF(condition, value_if_true, value_if_false)`

Text functions

`CONCAT(value1, value2, ...)`

Date and time functions

`NOW()`

`TODAY()`

Operators (core syntax)

Arithmetic

Comparison

4. Use AI to generate formulas

Step 1: Enable AI

Step 2: Describe logic

Step 3: Generate

5. Validation and feedback

6. Key concepts

6.1 Row-level calculation

6.2 Column references

6.3 Functions

7. Common use cases

Pricing

Revenue

Classification

Promotions

8. Best practices

Naming

Simplicity

Reusability

Performance

9. Limitations

10. Recommended workflow

11. Troubleshooting