Dashpivot supports Excel-style logical functions inside Default Tables and Prefilled Tables. These formulas are commonly used for:
Decision-making
Data validation
Error handling
Workflow automation
Dynamic reporting
Complex calculations
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
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
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
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
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
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.
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
FALSE()
Purpose: Returns the logical value FALSE.
Formula to use:
=FALSE()
Example:
=FALSE() returns FALSE
XOR()
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
Using logical formulas in Dashpivot helps automate workflows, reduce manual review, and ensure forms respond dynamically based on user input.