In this blog post, I’ll use examples to show you how to use some of the Excel logical functions, including the **IF**, **OR**, and **AND** functions, to name a few. Logical functions enable you to choose the outcome of a formula based on various criteria that you define.

## What Are Excel Logical Functions?

I have first discussed the Excel logical functions in part three of the Excel Itch series of blog posts introducing Excel formulas. If you are not familiar with Excel formulas, then I suggest reading this series of blog posts first by clicking here.

Excel logical functions are perhaps the most useful group of Excel functions. Logical functions differ from the other functions because they provide you with a degree of control over the result of a formula.

Excel logical functions are a mixture of Boolean logic and conditional tests. If you are not familiar with Boolean logic, click here for an explanation. The table below explains which logical functions belong to which group.

Function | Boolean or Conditional |
---|---|

OR | Boolean |

AND | Boolean |

XOR | Boolean |

NOT | Boolean |

TRUE | Boolean |

FALSE | Boolean |

IF | Conditional |

IFERROR | Conditional |

IFNA | Conditional |

The Boolean functions are often used in conjunction with other functions, particularly conditional test functions, to create formulas that can evaluate multiple criteria and produce different results depending on that criteria.

## Terminology

Before we start, it’s probably best to describe some of the terminology that will be used in this post.

Term | Description |
---|---|

Operand | An operand is a value (numeric, alpha, symbol, or some combination of the aforementioned items) that is used in a formula. The Operand can be entered directly into a Formula as text, can be accessed via a reference such as a Cell Reference, or can be the output of a Function. |

Operator | Operators describe the relationship and the action to be carried out between two parts of a Formula, usually the relationship between Operands. |

Expression | An Expression is a combination of Operands and Operators. A Formula can be an expression in its own right, or a formula can be made up of several expressions, often surrounded by parentheses. |

Evaluate | Determine or calculate the value of an expression or formula. |

Condition | A condition is something that can tested. An expression can evaluate a condition. |

FALSE | FALSE is one possible outcome when evaluating a condition. If the condition isn't met, then the result is referred to as FALSE. For instance, if A=6 and B=10, and you test if A is greater than B (A>B), the answer is no. The condition has been tested using the expression A>B, and it has been evaluated as FALSE. |

TRUE | TRUE is the other possible outcome when evaluating a condition. If the condition is met, then the result is referred to as TRUE. For instance, if A=10 and B=6 and you test if A is greater than B (A>B), then the answer is now yes. The condition has been tested using the same expression, A>B, but this time, it is evaluated as TRUE. |

## Accessing the Logical Functions

To access the logical functions, click the Formulas tab on the ribbon and then click the Logical button as shown in the image below.

## Excel IF Function

The **IF** function is one of the most commonly used functions. The IF function enables you to implement logic into a spreadsheet based on an evaluation of the value of an operand. In other words, test the value of X. If the value of X passes the test (returns **TRUE**), then do Y. If the value fails the test (returns **FALSE**), then do Z.

The syntax of the **IF** function is **IF(logical_test, value_if_true, [value_if_false])**.

Parameter | Description |
---|---|

logical_test | the logical_test parameter is the logical test that is carried out. |

value_if_true | the value_if_true parameter is what the IF function should do if the logical_test parameter returns TRUE. |

[value_if_false] | the value_if_false parameter is what the IF function should do if the logical_test parameter returns FALSE. The parameter is optional; if the parameter is excluded, the function does nothing if the logical_test parameter returns FALSE. |

Okay, let’s take a look an example. In the image below is a worksheet of company employees, admins, salespersons, and managers.

Only salespersons get paid commission, so we’re going to use the **IF** function to test the condition of “If Role = Salesperson then Calculate Commission” using the following formula:

**=IF(B5=”Salesperson”,D5*$B$1,0)**

Column B in the image above contains the employee’s role, and the **IF** function in column F uses the expression **B5=”Salesperson”** to test condition, **Role= Salesperson**. If the expression evaluates to **TRUE**, then the formula returns commission by multiplying the sales in column **D** by the rate of commission in cell **B1** using the expression **D5*$B$1**. If the expression **B5=”Salesperson”** evaluates to **FALSE**, then the formula returns zero.

## Excel OR Function

The **OR** function enables you to test the operands against predefined conditions. If the condition is met, then the **OR** function returns TRUE; otherwise, it returns **FALSE**. The **OR** function allows you to have up to 255 conditions. If any of the conditions are met, the **OR** function will return **TRUE**.

The syntax of the **OR** function is **OR(logical1, [logical2], …)**.

Parameter | Description |
---|---|

logical1 | the logical1 parameter is the first condition to be tested. This parameter is required. |

logical2 | the logical2 parameter is the second condition to be tested. The OR function can optionally have up to 255 different parameters, each with a different condition that can be tested. |

The following truth table shows possible outcomes for the **OR** function with two parameters.

logical1 | logical2 | Evaluates |
---|---|---|

TRUE | TRUE | TRUE |

TRUE | FALSE | TRUE |

FALSE | TRUE | TRUE |

FALSE | FALSE | FALSE |

The **OR** function is often used with the **IF** function to perform logical tests. As explained earlier in this blog post, the **IF** function enables you to implement logic that tests a condition: if the condition is **TRUE**, then do X; if the condition is **FALSE**, then do Y.

Because the **OR** function can test multiple conditions, it can be used in conjunction with the **IF** function to test multiple conditions. For example, using the previous example, we might want to pay commission to both salespersons and managers because they do a lot of sales as well. To do this, we could test the following condition: **“IF Role = Salesperson OR Manager then Calculate Commission,”** using the following formula:

**=IF(OR(B5=”Salesperson”, B5=”Manager”),D5*$B$1,0)**

As shown in the image below.

Column **B** contains the employee’s role. This time, the **IF** function in column F uses the expression **OR(B5=”Salesperson”, B5=”Manager”)**, to test if the role equals salesperson or manager. If either or both conditions evaluate to **TRUE**, the expression evaluates to **TRUE**, resulting in the formula calculating the commission by multiplying the sales in column **D** by the rate of commission in cell **B1** using the expression **D5*$B$1**. If none of the conditions in the expression **OR(B5=”Salesperson”, B5=”Manager”)** evaluate to **TRUE**, then it evaluates to **FALSE**, and the formula returns zero.

## Excel AND Function

The **AND** function also enables you to test the operands against predefined conditions. If all conditions are met, the **AND** function returns **TRUE**; otherwise, it returns **FALSE**. The **AND** function allows you to have up to 255 conditions. Only if all of the conditions are met will the **AND** function return **TRUE**.

The syntax of the **AND** function is **AND(logical1, [logical2], …)**.

Parameter | Description |
---|---|

logical1 | the logical1 parameter is the first condition to be tested. This parameter is required. |

logical2 | the logical2 parameter is the second condition to be tested. The AND function can optionally have up to 255 different parameters each with a different condition that can be tested. This parameter is optional. |

The following truth table shows the possible outcomes for the **AND** function with two parameters.

logical1 | logical2 | Evaluates |
---|---|---|

TRUE | TRUE | TRUE |

TRUE | FALSE | FALSE |

FALSE | TRUE | FALSE |

FALSE | FALSE | FALSE |

The **AND** function, like the **OR** function, is often used with the **IF** function to perform logical tests. Because the **AND** function can have multiple conditions, the **IF** function can be used to test multiple conditions. Using our example, let’s extend our spreadsheet. The admin employees get the chance to earn a little extra, so let’s pay them overtime. Additionally, let’s pay annual bonus. However, for our salespersons to earn a bonus, they need to hit a sales target of $200,000. Our worksheet now looks like the image below.

In the updated worksheet, we have added a new column for the number of hours worked in a year (column **D**) and a column for the bonus to be paid (column** I**). The values for bonus, sales target, and overtime rate have also been added to the worksheet in cells **B2**, **B3**, and **B5**, respectively.

Let’s start by calculating overtime by testing the condition **“IF Role=Admin AND Hours Worked are greater than Standard Annual Hours then Calculate Overtime”** using the formula:

**=IF(AND(B8=”Admin”,D8>$B$4),(D8-$B$4)*(C8*$B$5),0)**

The **IF** function uses the expression **AND(B8=”Admin”,D8>$B$4)** to test that the role in column **B** is equal to **Admin** and that the **Hours Worked** in column **D** are greater than the **Standard Annual Hrs** value in cell **B4**. If both conditions evaluate to **TRUE**, then the expression evaluates to **TRUE**, and the overtime is calculated. If any of the conditions don’t evaluate to **TRUE**, then the expression evaluates to **FALSE**.

Overtime is calculated using the expression **(D8-$B$4)*(C8*$B$5)**. The overtime expression first works out how many hours of overtime have been worked by taking away the **Standard Annual Hours** (cell **D8**) from **Hours Worked** (column **B**) to get the number of overtime hours worked. Then, to get the **Overtime Pay Rate**, the **Hourly Rate** of the employee in column **C** is multiplied by the **Overtime Rate** in cell **C8**. Finally, the number of overtime hours worked is multiplied by the **Overtime Pay Rate**.

To calculate the bonus, we’ll use a formula that contains the **IF**, **AND**, and **OR** functions to test the condition **“IF Role = Admin OR Manager OR Salespersons who have sales greater than Sales Target, then Calculate Bonus”** using the following formula:

**=IF(OR(B8=”Admin”,B8=”Manager”, AND (B8=”Salesperson”, G8>$B$3)), F8*$B$2,0)**

The **IF** function uses the expression **OR(B8=”Admin”, B8=”Manager”, AND(B8=”Salesperson”, G8>$B$3))** to test the condition. If any of the **OR** function conditions evaluate to **TRUE**, then the expression evaluates to **TRUE**. However, for the salespersons they must also exceed the sales target, and this is tested by testing the value of **Sales** in column **E** against the **Sales Target** in cell **B3**.

The **Bonus** is calculated using the expression **F8*$B$2**, which multiplies the **Base Salary** in column **F** by the **Bonus** percentage in cell **B2**.

## Excel XOR Function

**XOR** stands for **Exclusive OR**. For most users of Excel, the **XOR** function isn’t something that they will use, and it has really only been included here for completeness.

The **XOR** function can be a bit difficult to understand at first. When you have two parameters, the **XOR** function will return **TRUE** if either condition returns true. However, it will return **FALSE** if both or none of the conditions return **TRUE**.

The situation gets a little more complicated when you have more than two parameters. The **XOR** function will return **TRUE** if an odd number of conditions return **TRUE**. However, the **XOR** function will return **FALSE** if the total number of conditions that return **TRUE** are even or if all conditions return **FALSE**.

The syntax of the **XOR** function is **XOR(logical1, [logical2], …)**.

Parameter | Description |
---|---|

logical1 | the logical1 parameter is the first condition to be tested. This parameter is required. |

logical2 | The logical2 parameter is the second condition to be tested. The XOR function can optionally have up to 254 additional parameters, each with a different condition that can be tested. |

The following truth table shows possible outcomes for the **XOR** function with two parameters.

logical1 | logical2 | Evaluates |
---|---|---|

TRUE | TRUE | FALSE |

TRUE | FALSE | TRUE |

FALSE | TRUE | TRUE |

FALSE | FALSE | FALSE |

## Excel NOT Function

The **NOT** functions returns a logical value that is the opposite of a user-supplied logical value or output from another operator (expression or functions). Simply put, it returns **FALSE** if supplied with **TRUE** and returns **TRUE** if supplied with **FALSE**.

The syntax of the **NOT** function is **NOT(logical)**.

Parameter | Description |
---|---|

logical | the logical parameter is the value or output from another operator. This parameter is required. |

The following truth table shows possible outcomes for the **NOT** function with its single parameter.

logical | Evaluates |
---|---|

TRUE | FALSE |

FALSE | TRUE |

In the example below, we use an **IF** function and an expression to test “IF the time in a cell is greater than the opening time for a shop; if NOT, the shop is closed.” To do this, we will use the following formula:

**=IF(NOT(B1>”8.00am”),”Shop is Closed”, “Shop is Open”)**

As shown in the worksheet that is in the image below.

The time condition is tested with the following expression, **(B1>”8.00am”)**. This expression tests to see if the time in cell **B1** is greater than the opening time of 8.00am. If the time isn’t greater than 8.00am, then the expression evaluates to **FALSE**. However, to display the message “**Shop is Closed**” we need **TRUE**, so the **NOT** function flips the result to **TRUE**, and the message is displayed.

## Excel TRUE Function

The **TRUE** function simply returns the logical value **TRUE**.

The syntax of the **TRUE** function is simply **TRUE()**. Note that the **TRUE** function doesn’t have any parameters.

The image below is a simply shows the **TRUE** function in a cell returning a value of **TRUE**.

## Excel FALSE Function

Like the **TRUE** function, the **FALSE** function simply returns the logical value **FALSE**.

The syntax of the **FALSE** function is simply **FALSE()**. Note that the **FALSE** function doesn’t have any parameters.

The image below simply shows the **FALSE** function in a cell returning a value of **FALSE**.

## Excel IFERROR Function

Back in blog post two of the introduction to Excel Formulas, you were introduced to some of the errors that you may experience when using Excel formulas. The **IFERROR** function is one of the methods that you can use to manage these errors.

The syntax of the **IFERROR** function is **IFERROR(value, value_if_error)**.

Parameter | Description |
---|---|

value | The value that is displayed if there is no error. This is generally some kind of function that is calculating a value. |

value_if_error | The value that should be used if an error occurs. |

In the example below, the **IFERROR** function is used to detected an error and display a message. Cell **A1** contains the formula:

**=IFERROR(B1/C1,”Error Detected”)**

This formula divides cell **A2** by **A3**. Both cells **A2** and **A3** contains zeros which means zero divided by zero, in which case Excel will return the error **#DIV/0!**.

The cell highlighted in green displays the message “**Error Detected**“, which demonstrates the functionality. However, this result might not be much use if you’re trying to calculate numeric values. The cell highlighted in yellow displays zero “**0**” instead of “**Error Detected**“. In some circumstances, this might be of more use than an error message, especially if the result of this formula is used elsewhere in the workbook.

To learn more about errors and error handling in formulas, read my blog post by clicking here.

## Excel IFNA Function

The **IFNA** function is another useful function for handling errors. It is more specific than the **IFERROR** function and only tests to see if a formula or expression returns the **#N/A** error. If a formula does return the **#N/A** error, you can specify your own value to be returned instead. If a formula returns anything other than the **#N/A** error, then the **IFNA** function returns the original value of the formula even if it is another error value.

The syntax of the **IFNA** function is **IFNA(value, value_if_na)**.

Parameter | Description |
---|---|

value | the value parameter contains the expression that is checked for the #N/A error. This parameter is required. |

value_if_error | the value_if_error parameter contains the value that will be returned if the expression in the value parameter returns the #N/A error . This parameter is required. |

One practical example of when the **IFNA** is used when working with the **VLOOKUP** function.

If a **VLOOKUP** up function can’t find the lookup value in the specified range of cells, then it returns the **#N/A** error.

In the following example, an Excel user can enter a type of fruit in a cell **B1**. Cell **B2** contains the following formula:

**=IFNA(VLOOKUP(B1,A4:B7,2,0),”Enter a Valid Fruit”)**

This formula uses an expression containing the **VLOOKUP** function to search the first column of the range of cells **A4:B7** for the text entered by the user in cell **A2**. If the **VLOOKUP** function can’t find a fruit that matches the text entered, it returns the **#NA** error, not much use. By using the **IFNA** function, it detects when this error is returned and displays the text **“Enter a Valid Fruit”**.

In the example below, Grapefruit has been entered in cell **B1**. Because Grapefruit isn’t in the list of Fruit, the message **“Enter a Valid Fruit”** is displayed.

If you’re not familiar with the VLOOKUP function, then check out my blog post on the VLOOKUP function by clicking here.

**Wrap-up**

OK, I hope this post was informative and you’ve learned something useful. At first, Logical Functions can seem difficult to grasp. But once you’ve gotten used to them, they quickly become indispensable.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my my resources page.

To get a copy of the example spreadsheet used in this blog post, sign up for our newsletter below.