Dashpivot supports Excel-style logical functions inside Default Tables and Prefilled Tables. These formulas are commonly used for decision-making, data validation, error handling, and dynamic reporting.

This article covers each supported logical function with syntax, examples, and Dashpivot-specific tips.

AND(Logical1, Logical2, …)

Purpose: Returns TRUE if all conditions are TRUE.

Formula to use:
=AND(A1="Engineer",B1="Yes")

Example:
If A1 = Engineer
and B1 = Yes

=AND(A1="Engineer",B1="Yes") returns TRUE

If either condition is not met, the result returns FALSE

Note: Supports up to 30 logical arguments.

OR(Logical1, Logical2, …)

Purpose: Returns TRUE if at least one condition is TRUE.

Formula to use:
=OR(A1>10,B1<5)

Example:
If A1 = 8
and B1 = 3

=OR(A1>10,B1<5) returns TRUE

If both conditions are FALSE, the result returns FALSE

Note: Supports up to 30 logical arguments.

NOT(Logical)

Purpose: Reverses a logical value.

Formula to use:
=NOT(A1)

Example:
If A1 = TRUE
=NOT(A1) returns FALSE

If A1 = FALSE
=NOT(A1) returns TRUE

IF(Test, Value_if_true, Value_if_false)

Purpose: Performs a logical test and returns one value if TRUE and another if FALSE.

Formula to use:
=IF(A1>5,"Severe","Minor")

Example:
If A1 = 8
=IF(A1>5,"Severe","Minor") returns Severe

If A1 = 3
=IF(A1>5,"Severe","Minor") returns Minor

💡 Dashpivot tip — Yes/No and List fields: Yes/No fields in Dashpivot are List fields that store the string "yes" or "no" — not a boolean TRUE or FALSE. To reference them correctly in an IF formula, compare against the string value:

✅ Correct: =IF(A1="yes","Pass","Fail")
❌ Incorrect: =IF(A1=TRUE(),"Pass","Fail")

The same applies to any List field — compare against the option's text value as a string.

IFS(Condition1, Value1, Condition2, Value2, …)

Purpose: Evaluates multiple conditions and returns the value for the first TRUE condition.

Formula to use:
=IFS(A1>=80,"Pass",A1<80,"Fail")

Example:
If A1 = 85
=IFS(A1>=80,"Pass",A1<80,"Fail") returns Pass

If A1 = 70
=IFS(A1>=80,"Pass",A1<80,"Fail") returns Fail

💡 Dashpivot tip — adding a default/else: IFS has no built-in else argument. If no condition is met, the formula returns #N/A. To avoid this, add TRUE, "default value" as the final pair:

=IFS(A1>=80,"Pass",A1<50,"Fail",TRUE,"Review")

This ensures a result is always returned.

SWITCH(Expression, Value1, Result1, …, Otherwise)

Purpose: Compares a value against a list of matches and returns the corresponding result.

Formula to use:
=SWITCH(A1,90,"A",80,"B","No Match")

Example:
If A1 = 90
=SWITCH(A1,90,"A",80,"B","No Match") returns A

If A1 = 75
=SWITCH(A1,90,"A",80,"B","No Match") returns No Match

💡 Dashpivot tip: The final argument acts as the default/else result when no match is found. Always include it to avoid a #N/A error when the expression doesn't match any listed value.

IFERROR(Value, Alternate_value)

Purpose: Returns an alternate value if a formula results in an error.

Formula to use:
=IFERROR(A1/B1,"Error")

Example:
If A1 = 10
and B1 = 0

=IFERROR(A1/B1,"Error") returns Error

If no error occurs, it returns the calculated result.

Note: IFERROR catches all error types. Use IFNA if you only want to catch #N/A errors.

IFNA(Value, Alternate_value)

Purpose: Returns an alternate value if a formula results in a #N/A error.

Formula to use:
=IFNA(A1,"Not Available")

Example:
If A1 results in #N/A
=IFNA(A1,"Not Available") returns Not Available

If A1 does not contain #N/A, it returns the original value.

TRUE()

Purpose: Returns the logical value TRUE.

Formula to use:
=TRUE()

Example:
=TRUE() returns TRUE

Important: TRUE must be written with parentheses — TRUE(). Writing bare TRUE without parentheses returns a #NAME? error.

FALSE()

Purpose: Returns the logical value FALSE.

Formula to use:
=FALSE()

Example:
=FALSE() returns FALSE

Important: FALSE must be written with parentheses — FALSE(). Writing bare FALSE without parentheses returns a #NAME? error.

XOR(Logical1, Logical2, …)

Purpose: Returns TRUE if an odd number of conditions are TRUE.

Formula to use:
=XOR(A1>10,B1<5)

Example:
If A1 = 12
and B1 = 3

=XOR(A1>10,B1<5) returns FALSE

(Both conditions are TRUE, so the count is even.)

If only one condition is TRUE, the result returns TRUE

Which field types work in logical formulas?

Logical formulas can only produce meaningful results when referencing field types that return a usable value. Here is how each field type behaves inside IF, AND, OR, and similar conditions:

Field type	Value in formula	Example condition
Number	Numeric value	`=IF(A1>10,"High","Low")`
Text / Prefilled Text	String value	`=IF(A1="Engineer","Yes","No")`
List (including Yes/No)	String value of selected option (e.g. `"yes"`, `"no"`)	`=IF(A1="yes","Pass","Fail")`
List Property	String representation of the property value	`=IF(A1="Active","Yes","No")`
Date / Date Plain	ISO date string; date comparisons work	Use DATEDIF for date calculations
Formula	Result of that formula cell	`=IF(A1>0,"Positive","Zero")`
Attachment, Photo, Image, Signature, Location, Person	Returns blank / null	❌ Not suitable for logical conditions

Nesting limit

Logical formulas can be nested inside each other (e.g. =IF(IF(...),...)), but Dashpivot supports a maximum of 6 levels of nested parentheses. Formulas with 7 or more levels will show a #NESTED! error in the template editor. See Current formula limitations in Dashpivot for more detail.

Common errors in logical formulas

Error	Cause	Fix
`#VALUE!`	Wrong argument type — e.g. comparing a text cell numerically	Check that comparisons match the field type (string vs number)
`#NAME?`	Bare `TRUE` or `FALSE` without parentheses; mistyped function name	Use `TRUE()` and `FALSE()` with parentheses
`#NESTED!`	Formula has more than 6 levels of nested parentheses	Simplify the formula or split logic across multiple formula cells
`#N/A`	IFS with no matching condition and no default pair	Add `TRUE, "default"` as the final IFS argument
`#REF!`	Referencing a column or table that doesn't exist	Check the cell reference is correct and the column exists

❓ Frequently asked questions

Can I use a Yes/No field inside an IF formula in Dashpivot?

Yes, but you must compare against the string value, not a boolean. Yes/No fields store "yes" or "no" as text. Use =IF(A1="yes","Pass","Fail") — not =IF(A1=TRUE(),"Pass","Fail").

What happens if no condition is TRUE in an IFS formula?

The formula returns #N/A. To avoid this, add TRUE, "default value" as the final pair in your IFS formula to catch all remaining cases.

Why does my formula return #NAME? when I use TRUE or FALSE?

In Dashpivot formulas, TRUE and FALSE must be written as functions with parentheses: TRUE() and FALSE(). Writing bare TRUE or FALSE without parentheses is not recognised and returns a #NAME? error.

How many nested IF statements can I use in Dashpivot?

Dashpivot supports up to 6 levels of nested parentheses. A formula with 7 or more levels will return a #NESTED! error. If you need more logic branches, consider splitting the formula across multiple formula cells or using IFS instead of nested IFs.

Using logical formulas in Dashpivot helps automate workflows, reduce manual review, and ensure forms respond dynamically based on user input.

Logical formulas in Dashpivot (with syntax and examples)