Logical functions

In Spreadsheet, you can use a series of logical functions to return a corresponding result or run corresponding logic based on whether a specified condition is met.

​

IF function

The IF function is the most basic logical function. It takes 3 arguments:

IF(condition, formula to run if the condition is met, formula to run if the condition is not met)

• The “condition” can be any value or a nested formula. When evaluating, the function attempts to convert the “condition” value to TRUE (met) or FALSE (not met), and runs the corresponding logic based on TRUE or FALSE.
  • FALSE, the number 0, empty text, empty cells, and errors are converted to FALSE; all other values are converted to TRUE.

Example: Mark as “Pass” when “Score” reaches 60.

​

Combined conditions: AND function checks whether multiple conditions are met simultaneously

As the name suggests, “AND” means “and both/all”, so the AND function checks whether multiple conditions are met at the same time. It accepts multiple arguments, each corresponding to a condition. If all are met (TRUE), the function returns TRUE; otherwise, it returns FALSE. By nesting the AND function as the condition inside an IF function, you can handle the following scenario: Example: Mark as “Pass” when both “Listening” and “Reading” scores are above 60.

​

Combined conditions: OR function checks whether at least one of multiple conditions is met

Similarly, “OR” means “either”. The OR function checks whether at least one of multiple conditions is met. It also accepts multiple arguments, each corresponding to a condition. If any one of the conditions is met (TRUE), the function returns TRUE; if none are met, it returns FALSE. When nested inside the IF function, it enables: Example: Mark as “Pass” if either “Listening” or “Reading” reaches 60.

​

Combined conditions: IFS function runs different logic for multiple conditions

The IF function has two execution branches based on whether a single “(combined) condition” is met. The IFS function, in contrast, lets you specify a series of conditions to evaluate in order. When a condition is met, it returns the corresponding result or runs the corresponding logic. Example: Mark the corresponding “Grade” based on the range that the “Score” falls into.

​

IFERROR function

The IFERROR function takes 2 arguments. Its logic is to check whether the 1st argument is an “error”: if so, the function returns the 2nd argument; otherwise, it returns the 1st argument as is. Here, “error” refers to values such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

​

IFNA function

Similar to the IFERROR function, but the IFNA function only checks whether the 1st argument is a #N/A error. It is often used together with lookup functions such as VLOOKUP and MATCH, because these functions return a #N/A error when the target value is not found. Example: When a nested VLOOKUP function does not find the “Product Name” corresponding to “dd233”, it returns a #N/A error. The IFNA function receives the #N/A argument and therefore returns “Not found”.

---

Back to Spreadsheet