Logical Functions

Introduction

Logical functions are used to test whether a situation is true or false. Depending on the test result, you can elect to do one thing or another. You can also customize the logical values.

Here are some basic concepts:

AND

AND(logical1, logical2, ...)

Returns false if any of the provided arguments are logically false in the argument set and returns true if all of the provided arguments are logically true in the argument set.

The AND function is to require two or more logical equations in the brackets to meet true (comma-separated logical equations are met), and then the returns 1, which means true.

AND(A,B) indicates that the conditions A,B are met at the same time, so it returns true, otherwise it returns false.

For example:

1. AND(1<5,1<6), returns true.

2. AND(1<5,7<6), returns false.

Here is one example of combining the AND function with the IF function: Return Excellent when all scores of the three subjects are above 90, otherwise return Other. Set the Formula as follows:

IF(AND(Biography Score>90,Math Score>90, English Score>90), “Excellent”, “Other”)

FALSE

FALSE()

Returns the logical value FALSE.

The FALSE function can be used with other functions or as the result of a logical evaluation. For example, in warehouse management, to determine if the inventory quantity matches the inventory count, you can set the Formula as IF(Inventory Quantity==Inventory Count,TRUE(),FALSE()). This will return true if the quantity matches the count, and false otherwise.

IF

IF(logical_test, value_if_true, value_if_false)

How to judge whether a condition can be met: It returns a value if the logical condition is TRUE, otherwise it will return another value.

There are three arguments in the IF function:

1. Basic usage of the IF function.

IF(A,B1,B2) indicates that it returns B1 if condition A is TRUE, otherwise it returns B2.

For example, in a Math exam, set the function to return Fail if the score is below 60; otherwise, return Pass: IF(Math Score<60, "Fail", "Pass").

2. Multiple IF functions can be nested together for multiple criteria.

For example, in a Math exam, set the function rule to return Fail if Math scores are below 60, return Medium if Math scores are between 60-79, return Good if Math scores are between 80-89, and return Excellent if Math scores are above 90. Then you can set the Formula as follows:

IF(Math Score<60, "Fail", IF(Math Score<80, "Medium", IF(Math Score<90, "Good", "Excellent")))

3. Apply the IF function to the Single Select/Radio fields.

What is one plus three?

A. Four

B. Six

C. Eight

For example, you can set the rule as choosing A to get 3 points, otherwise 0 points. Then you can set the Formula as follows:

IF(What is one plus three? == "A. Four",3,0)

4. Apply the IF function to the Multi Select/Checkbox fields.

Which of the following statements are correct?

A. The admin can create a form on the app management page.

B. You can create a form by importing Excel files.

C. You can copy a form from the current app or other apps.

For this question, you can set the rule as choosing ABC to get 3 points, otherwise 0 points. Then you can set the Formula as follows:

IF(Which following statements are correct? =="A. The admin can create a form on the app management page, B. You can create a form by importing Excel files, C. You can copy a form from the current app or other apps",3,0)

IFS

IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ... ,logical_testn, value_if_truen)

The IFS function checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition. IFS can replace multiple nested IF statements and is easier to read when there are multiple conditions.

The IFS function has the following arguments:

Note:

The maximum value of n is 127. The IFS function allows you to test up to 127 different conditions.

IFS(A1,B1,A2,B2,A3,B3...) indicates that return B1 when condition A1 is met, return B2 when condition A2 is met, and return B3 when condition A3 is met, returns B3.

For example, the IFS function can be used to calculate the grade of the scores. You can set the Formula as follows:

IFS(Math Score<60,"Failed",Math Score<=79,"Pass",Math Score<=89,"Good",Math Score>=90,"Excellent")

NOT

NOT(logical)

Reserves the logical value of its argument.

NOT is a simple function. It means that NOT returns TRUE if the logical value is False and NOT returns FALSE if the logical value is TRUE.

For example:

1. NOT(50<60), returns false.

2. NOT(90<60), returns true.

Here is one example of combining the NOT function with the IF function: Return Pass when the score is above 60, otherwise it returns Make up the exam. Then you can set the Formula as follows:

IF(NOT(Score<60),"Pass","Make up the exam")

OR

OR(logical1,logical2, ...)

Returns true if any of the provided arguments are logically true in the argument set and returns false if all of the provided arguments are logically false in the argument set.

OR(A,B) returns true if condition A or B is met (one or more of the conditions are met), otherwise it returns false.

For example:

1. OR(1<2,2<2), returns true.

2. OR(3<2,2<2), returns false.

Here is one example of combining the OR function with the IF function: Return Excellent if any one of the scores is above 90, otherwise return Other. Then you can set the Formula as follows:

IF(OR(Biology Score>90,Math Score>90,English Score>90),"Excellent","Other")

TRUE

TRUE()

Returns the logical value TRUE.

The TRUE function can be used with other functions or as the result of a logical evaluation. For example, to determine whether a field values is empty, you can set the Formula as IF(ISEMPTY(Project)==TRUE(),"Not selected","Selected"). When the value is empty and evaluates to TRUE(), it will return Not selected, otherwise it will return Selected.

XOR

XOR(logical1,logical2, ...)

Returns an exclusive or of all arguments.

Exclusive or: It returns false if two logical values are the same, otherwise it returns true.

For example, the formula XOR(Answer 1<90, Answer 2<90) tests whether the two values are the same with each other. As shown in the figure below, Answer 1 is more than 90 and Answer 2 less than 90. Therefore, only one of the two values is true and the function returns True.