IF() function limitations

The IF() function in custom column formulas has a restriction: only addition (+), subtraction (-), and multiplication by a constant are allowed inside the IF() branches.

The Limitation

When creating a custom column formula with division inside IF():

IF(category = "A", metric_a / metric_b, 0)

You will get the error:

Only +, - and multiplication by a constant are allowed in IF() function

Why This Limitation Exists

The IF() function is internally computed as the sum of two Filter() calls:

IF(condition, a, b) === Filter(condition, a) + Filter(NOT condition, b)

This decomposition can lead to unexpected results with non-additive operations. Consider this example:

IF(key = "a", 1, 2)

This is computed as:

Filter(key = "a", 1) + Filter(key != "a", 2) = 1 + 2 = 3

The result is 3, not 1 or 2 as you might expect from a traditional if-else statement. To prevent such confusion, division and other non-additive operations are restricted in IF().

The Solution: Use Filter()

If you need division or other complex operations conditionally, use the Filter() function instead:

Filter(category = "A", metric_a / metric_b)

The Filter() function computes the child expression as if there was an additional filter applied to the entire table. It does not have the same limitations as IF().

Example

Goal: Calculate a ratio only for rows where status = "active"

This will NOT work (produces an error):

IF(status = "active", revenue / cost, 0)

Use this instead:

Filter(status = "active", revenue / cost)