AND, OR, and NOT Functions: Combine Conditions
You can use AND, OR, and NOT functions to determine if multiple conditions are met.
The evaluation of these functions can also be used together with the IF function.
Function syntax
The AND function returns TRUE if all of the specified conditions are TRUE. Otherwise, it returns FALSE.
AND(condition1,condition2, ...)The OR function returns TRUE if any of the specified conditions are TRUE. If all of the conditions are FALSE, it returns FALSE.
OR(condition1,condition2, ...)The NOT function returns TRUE if the specified condition is FALSE. It returns FALSE if the condition is TRUE.
NOT(condition1)Operators that can be used in conditions
The following operators can be used to specify conditions.
- =
- !=
- <>
- <
- <=
- >
- >=
When comparing values of numeric type fields (such as "Number" fields), you can use all of the operators above.
When comparing values of string type fields (such as "Text" fields), you can only use the following operators: = != <>
For information on field data types, refer to Data Types of Fields.
Formula examples
When specifying a field in a formula, make sure to specify the field code as the argument (and not the field name).
In the examples below, the field code is set for each field as the field name with underscores instead of spaces (e.g., the field code for the "Practical Skill" field is "Practical_Skill").
Formula to display "Pass" if two items both score 80 or higher
The following formula displays "Pass" if both the "Written" and "Practical Skill" fields contain scores of 80 or higher, and "Retest" if not.
IF(AND(Written>=80,Practical_Skill>=80),"Pass","Retest")
The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.
Formula to display "Pass" if at least one of two items score 80 or higher
The following formula displays "Pass" if either the "Written" or "Practical Skill" field contains a score of 80 or higher, and "Retest" if not.
IF(OR(Written>=80,Practical_Skill>=80),"Pass","Retest")
The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.
Formula to display "Retest" if the sum of the scores of two items is not 160 or higher
The following formula displays "Retest" if the sum score of the "Written" and "Practical Skill" fields is not 160 or higher, and "Pass" if it is.
IF(NOT(Written+Practical_Skill>=160),"Retest","Pass")
The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.
Formula to display "1" in the "Employment Status" field for current employees
The following formula checks if a person is currently employed at a company by referring to the "Hire Date" and "Termination Date", and displays "1" in the "Employment Status" field for current employees.
This formula is set in such a way as to return "1" if the "Hire Date" field is populated and the "Termination Date" field is blank, and "0" otherwise.
Because we want to use an empty value as a condition, the following is specified as the condition: field_code=""
IF(AND(Hire_Date!="",Termination_Date=""),1,0)
If you want the calculation result to be a numeric value, set the formula in a "Calculated" field. If you want the calculation result to be a string value, set the formula in a "Text" field.
For information on field data types, refer to Data Types of Fields.