Formula Columns

​

1. What is a formula column?

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

​

2. How to create a formula column

​

Step 1: Add column

• Click Columns → Add Column

​

Step 2: Select type

• Choose Formula

​

Step 3: Configure column

​

1. Name

• Margin %
• Revenue
• Price Index

​

2. Display format

​

3. Build formula

​

3. Building formulas

​

3.1 Manual input

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

​

3.2 Insert column references

• Click “Insert column reference”
• Select fields from the list

{Price} * {Quantity}

​

3.3 Insert functions

• Click “Insert function”
• Choose from list\

ROUND({Price}, 2)

​

Math and numeric functions

​

ROUND(value, digits)

ROUND({Price}, 2)

​

TRUNC(value, digits)

TRUNC({Price}, 0)

​

PRODUCT(value1, value2, ...)

PRODUCT({Price}, {Quantity})

​

POWER(base, exponent)

POWER({Price}, 2)

​

RAND()

RAND()

​

Aggregation functions

​

SUM(range)

SUM({Price})

​

SUMIF(range, condition)

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

​

MIN(range)

MIN({Price})

​

MAX(range)

MAX({Price})

​

AVERAGE(range)

AVERAGE({Price})

​

MEDIAN(range)

MEDIAN({Price})

​

Counting functions

​

COUNT(range)

COUNT({Price})

​

COUNTA(range)

COUNTA({Product Name})

​

COUNTBLANK(range)

COUNTBLANK({Price})

​

COUNTIF(range, condition)

COUNTIF({Category}, "Electronics")

​

Logical functions

​

IF(condition, value_if_true, value_if_false)

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

​

Text functions

​

CONCAT(value1, value2, ...)

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

​

Date and time functions

​

NOW()

NOW()

​

TODAY()

TODAY()

​

Operators (core syntax)

​

Arithmetic

+   addition
-   subtraction
*   multiplication
/   division

​

Comparison

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

​

4. Using AI to generate formulas

​

Step 1: Enable AI

• Toggle AI ON

​

Step 2: Describe logic

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

​

Step 3: Generate

• Click Generate by AI

• Create formula
• Validate syntax

​

5. Validation and feedback

• ✅ Formula is valid → ready to use
• ❌ Errors → fix syntax or references

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

​

6. Key concepts

​

6.1 Row-level calculation

{Price} * {Quantity}

​

6.2 Column references

{Price}

Price

​

6.3 Functions

• Aggregation
• Logic
• Transformation

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

​

7. Common use cases

​

Pricing

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

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

​

Revenue

{Price} * {Quantity}

​

Classification

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

​

Promotions

({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

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

​

11. Troubleshooting