Logical Functions

A logical function is a type of function in a formula that performs logical operations on data.

IF()

Determines whether a condition is met. If yes, the first value is returned. If not, the second value is returned.

IF(logical, value1, value2);

Parameter Description

logical: is a judgment condition whose expression outputs both true and false values. value1: The value returned when the condition is true. value2: the value returned when the condition is false.

Note ① : IF() supports nesting and can be used to check if the cell is empty.

Example

// Compare values. For example, in the following example, the {grade} field is of type number and the cell value is 80:
Formula: IF({grade} > 60, "pass "," fail ")
Result: "Pass"

// Compare the text, for example, when the {task progress} field is of radio type and the cell value is "completed" :
Formula: IF({task progress} = "done", "done", "in progress")
Result: "done"

// Compare dates. For example, the {deadline} field is of date type and the cell value is later than today:
Formula: IF(IS_AFTER({deadline},TODAY()), "not expired "," expired ")
Result: "Not expired"

// Determine null values, for example, when the {article content} field is text type and the cell value is null:
Formula: IF({text} = BLANK(), "blank "," written ")
Result: "Null"

// Nested multiple levels of criteria. For example, in the following example, the field {grade} is of type number and the cell value is 70:
Formula: IF ({grades} > 60,  IF ({results}  < 80, "medium", "excellent"), "fail")
Result: "medium"

// If multiple criteria hold simultaneously, print true.  For example, in the following example, the field {grade} is of type number and the cell value is 90:
Formula: IF({grade} > 80 &&{grade} <= 100, "Excellent "," other ")
Result: "Excellent"

// Output true if any of the criteria is true.For example, in the following example, the {grade} field is of numeric type and the cell value is 70, and the {talent} field is of text type and the cell value is "A" :
Formula: IF ({grades} > 80 | | {title} = "A", "excellent", "other")
Result: "Excellent"

SWITCH()

This function is a multi-branch selection function, which consists of an expression + multiple (branches + return values). If the expression is equal to a branch value, the function outputs the corresponding return value for that branch.

SWITCH(expression, [pattern, result… ],[default])

Parameter Description

expression: is an expression whose result is matched to each branch. pattern: indicates branches. Each branch represents the possible result of the expression.Each branch has a return value. result: is the return value. If the result of the expression matches a branch, the corresponding return value is printed. default: This parameter is optional and is the default value. If the operation result does not match any branch, the function outputs the default value. This parameter is null if the default value is not filled in.

Note ① :[pattern, result...] Indicates that multiple combinations of branches and return values can be filled.

Example

// Matches the text value.The first "C" in the following is expression. "A" and "excellent" are the pattern and result of A group, respectively. It indicates that "excellent" is output if expression is "A".
Formula: SWITCH (" C ", "A", "excellent", "B", "medium", "C", "general", "D", "poor", "no results")
Result: "Normal"

// Matches the value in the text type field {score}.When the value in the cell is "C" :
Formula: SWITCH ({scale}, "A", "excellent", "B", "medium", "C", "general", "D", "poor", "no results")
Result: "Normal"

// is used with the IF() function.When the following fields {country} and {region} are text types and the value in the cell is "China" and "Guangdong" :
Formula: SWITCH ({country}, "China", the IF ({in} = "guangdong province", "cantonese", "other"), "Russia", "Russian", "French", "French", "other")
Result: "Cantonese"

TRUE()

Returns the logical value true.

TRUE()

Parameter Description

This function requires no parameters.

This function can determine whether the column of the selected type is "selected", as shown in Example 1. This function, used with FALSE(), can print both true and false Boolean values, as shown in Example 2.

Example

// Determine the status of the fields of the selected type.For example, if the following {done} field is checked and the cell value is "unchecked" :
Formula: IF(({ completed } = FALSE()), "not completed", "completed ");
Result: "Not completed";

// TRUE() and FALSE() are used together as output boolean values.For example, if the following {grade} field is of numeric type and the cell value is 50:
IF({ score } > 60, TRUE(), FALSE());
Result: false;

FALSE()

Returns the logical value false.

FALSE()

Parameter Description

This function requires no parameters.

This function can determine whether the column of the selected type is "not selected", as shown in Example 1. This function is used with TRUE() to print both true and false Boolean values, as shown in Example 2.

Example

// Determine the status of the fields of the selected type.For example, if the following {done} field is of checked type and the cell value is "checked" :
Formula: IF(({ done } = TRUE()), "done", "not done ");
Result: "Done";

// TRUE() and FALSE() are used together as output Boolean values.For example, if the following {grade} fields are numeric and the cell value is 70:
IF({ score } > 60, TRUE(), FALSE());
Result: true;

AND()

Returns true (true) if all arguments are true, false (false) otherwise.

AND(logical1, [logical2, …])

Parameter Description

logical: is one or more logical conditions.

Example

// Outputs true if more than one logical condition is true.
Formula: AND(3 > 2, 4 > 3)
Result: true

// Output false if one of the logical conditions is not true.
Formula: AND(3 > 2, 4 < 3)
Result: false

// Determine whether all fields of the numeric type meet the logical conditions.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell values are 70 and 80, respectively:
Formula: AND({math score} > 60, {Chinese score} > 60)
Result: true

// is used as a criterion for the IF() function.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell values are 70 and 80, respectively:
Formula: IF(AND({math score} > 60, {Chinese score} > 60), "pass "," fail ")
Result: "pass"

OR()

Returns true (true) if either argument is true, false (false) otherwise.

OR(logical1, [logical2, …])

Parameter Description

logical: is one or more logical conditions.

Example

// Outputs true if more than one logical condition is true.
Formula: OR(3 > 2, 4 > 3)
Result: true

// Output true if one of the logical conditions is not true.
Formula: OR(3 > 2, 4 < 3)
Result: true

// Output false if none of the logical conditions are true.
Formula: OR(3 < 2, 4 < 3)
Result: false

// Determine whether a numeric field satisfies any of the logical conditions.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell value is 50 and 80, respectively:
Formula: OR({math score} > 60, {Chinese score} > 60)
Result: true

// is used as a criterion for the IF() function.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell value is 50 and 80, respectively:
Formula: IF(OR({math score} > 60, {Chinese score} > 60), "pass "," fail ")
Result: "pass"

XOR()

Returns true (true) if an odd number of arguments are true, false (false) otherwise.

XOR(logical1, [logical2, …])

Parameter Description

logical: is one or more logical conditions.

Example

// Outputs true if an odd number of logical conditions are true.
Formula: XOR(3 > 2, 4 < 3)
Result: true

// Output false if an even number of logical conditions are true.
Formula: XOR(3 > 2, 4 > 3)
Result: false

// Check whether a field of a numeric type satisfies an odd number of logical conditions.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell value is 50 and 80, respectively:
Formula: XOR({math score} > 60, {Chinese score} > 60)
Result: true

// is used as a criterion for the IF() function.For example, when the following fields {mathematics score} and {Chinese score} are numeric types and the cell value is 50 and 80, respectively:
Formula: IF(XOR({math score} > 60, {Chinese score} > 60), "pass "," fail ")
Result: "pass"

BLANK()

Indicates a null value.

BLANK()

Parameter Description

This function requires no parameters.

This function can be used to determine whether a cell is null, as shown in Example 1. This function replaces the contents of a cell with a null value, as shown in Example 2. Note ① : Null value is not zero value (0).

Example

// Determine if the cell is null.The following field {start time} is of date type and the cell value is null.
Formula: IF({start time} = BLANK(), "start time determined "," start time determined ")
Result: "Time not yet determined"

// Replace the original contents of the cell with null values.The following field {price} is of numeric type and the cell value is 0.
Formula: IF({price} = 0, BLANK(), {price})
Result: Null

ERROR()

Displays error messages and messages within cells.

ERROR(message);

Parameter Description

message: Optional, Error message to be printed. The default value is "#Error!

Example

// Directly output the error message without filling in the error message.The following field {age} is of numeric type and the cell value is -1.
Formula: IF({age} < 0, ERROR(), "normal ")
Result: #Error! // Enter the error information and display the error message.The following field {age} is of numeric type and the cell value is -1.
Formula: IF({age} < 0, ERROR(" statistical error "), "normal ")
Result: #Error: statistical error

IS_ERROR()

Checks if an expression is running incorrectly and returns true if it is.

IS_ERROR(expression);

Parameter Description

expression: indicates the expression to be checked.Expressions can be arithmetic operations, logical judgments, and so on.

Example

// There is an error in the arithmetic.
Formula: IS_ERROR(2 / 0);
Result: true;

// There is an error mixing text with numbers.
Formula: IS_ERROR(" ha ha " * 2);
Result: true;

NOT()

Reverses the logic condition.

NOT(logical);

Parameter Description

FALSE();

This function inverts the logical condition, for example: As in example 1: 'NOT(2 > 3)' is actually judged 2 <= 3 after inversion; Example 2: 'NOT({age} > 18)' after reversal, it is actually judged {age} ≤ 18.

Example

// Reverse the logical condition.As in the following, after inversion, the actual judgment is 2 <= 3.
Formula: NOT(2 > 3);
Result: true;

// Invert the logical condition by introducing a numeric field {age}.As the following is reversed after the actual judgment {age} ≤ 18. When the value in the cell is 12.
Formula: NOT({ age } > 18);
Result: true;