Comparisons & boolean operators
Comparisons and boolean operators turn values into conditions. Use them to test values, combine conditions, and drive branching logic, most commonly inside IF.
They return 1 for true and 0 for false.
Start here if you want to:
- Write a condition such as
'Revenue' > 1000 - Combine conditions with
AND,OR,NOT,XOR - Use a
TRUE/FALSEhelper function - Write the condition part of
IF(condition, value_if_true, value_if_false)
Not here if you want to:
- Search the catalog for a function page by name such as
GT,EQ,AND, orIS_NA→ see Logical functions - Find branching behavior and examples for
IFspecifically → see IF
Mental model
- Comparisons between two nodes run over the union of both inputs’ intersections, not only the rows they share, and return 1 (true) or 0 (false)
- Boolean operators combine or invert conditions and also return 1 or 0
TRUE('Node')/FALSE('Node')returns 1/0 wherever'Node'is definedIS_NA('Node')returns 1 where'Node'is undefined (and can expand result size)- In
IF, 0 is interpreted as false and any other value as true
Comparison operators
| Operator | Function equivalent | Details | Returns 1 when… |
|---|---|---|---|
= | EQ('Node1', 'Node2') | EQ (=) | values are equal |
!= | NEQ('Node1', 'Node2') | NEQ (!=) | values are not equal |
> | GT('Node1', 'Node2') | GT (>) | left is greater than right |
>= | GTE('Node1', 'Node2') | GTE (>=) | left is greater than or equal to right |
< | LT('Node1', 'Node2') | LT (<) | left is less than right |
<= | LTE('Node1', 'Node2') | LTE (<=) | left is less than or equal to right |
Many comparisons can be written either as a function call or as an operator expression, for example GT('Node1', 'Node2') or 'Node1' > 'Node2'.
Comparisons cannot be chained: at most one =/!= and one </<=/>/>= may appear per expression level, so 0 < 'Revenue' < 100 and 'A' = 'B' = 'C' are syntax errors. Write (0 < 'Revenue') && ('Revenue' < 100) instead.
Boolean operators
&& and \|\| are the only boolean infix operators. AND, OR, NOT and XOR exist as functions only, so they always need parentheses and comma-separated arguments. These are most commonly used inside IF conditions.
Defined/undefined helper functions
Common patterns
Flag positive values
Returns 1 where the value is above zero, else 0.
'Value' > 0
Combine conditions
True when either condition holds.
('Margin' < 0) || ('Revenue' = 0)
Branching logic
Returns one value where the condition holds, another where it does not.
IF('Net Income' > 0, 'Net Income' * 'PayoutRate', 0)
Truthiness shortcut
Treats any non-zero value as true.
IF('Delta', 1, 0)
Check whether a node is defined
Uses TRUE as the condition to select defined cells.
IF(TRUE('Sales'), 'Node1', 'Node2')
Find missing intersections
Returns 1 where the node is undefined.
IS_NA('Sales')
Pitfalls & troubleshooting
- Truthiness surprises: in conditions,
0is false and any non-zero value is true - Defined vs undefined:
TRUE('Node')andFALSE('Node')apply where the node is defined; useIS_NA('Node')to detect undefined intersections - Cube size risk:
IS_NAcan expand result size significantly - Missing is compared as zero, not skipped: when two nodes are compared, a side with no row at an intersection is treated as
0and the comparison still returns a defined 1 or 0.'A' > 'B'therefore returns 1 wherever'A'is positive and'B'has no row, and a comparison never returns N/A. UseIS_NAto tell a real zero from a missing value. The same coercion applies to&&,\|\|andXOR - A plain number behaves differently: comparing a node against a constant, as in
'Revenue' > 0, broadcasts the constant over the rows the node already has. No rows are added, so intersections where the node is undefined stay undefined - Dimensional mismatch: some logical functions, such as
XOR, may behave unexpectedly when inputs differ in granularity
Related
- IF: how conditions are interpreted in
IF - Formula basics: parentheses, evaluation order, and quoting rules
- Troubleshooting guide: wrong shape, wrong numbers, empty output, and missing values