How to Use the Excel Logical Functions

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.

FunctionBoolean or Conditional
ORBoolean
ANDBoolean
XORBoolean
NOTBoolean
TRUEBoolean
FALSEBoolean
IFConditional
IFERRORConditional
IFNAConditional

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.

TermDescription
OperandAn 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.
OperatorOperators describe the relationship and the action to be carried out between two parts of a Formula, usually the relationship between Operands.
ExpressionAn 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.
EvaluateDetermine or calculate the value of an expression or formula.
ConditionA condition is something that can tested. An expression can evaluate a condition.
FALSEFALSE 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.
TRUETRUE 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.

Opening Logical Functions on the Ribbon

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]).

ParameterDescription
logical_testthe logical_test parameter is the logical test that is carried out.
value_if_truethe 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.

IF Function

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], …).

ParameterDescription
logical1the logical1 parameter is the first condition to be tested. This parameter is required.
logical2the 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.

logical1logical2Evaluates
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

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.

Excel OR Function

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], …).

ParameterDescription
logical1the logical1 parameter is the first condition to be tested. This parameter is required.
logical2the 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.

logical1logical2Evaluates
TRUETRUETRUE
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

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.

New Worksheet with AND Function

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], …).

ParameterDescription
logical1the logical1 parameter is the first condition to be tested. This parameter is required.
logical2The 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.

logical1logical2Evaluates
TRUETRUEFALSE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

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).

ParameterDescription
logicalthe 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.

logicalEvaluates
TRUEFALSE
FALSETRUE

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.

NOT Function

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.

TRUE Function

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.

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
valueThe value that is displayed if there is no error. This is generally some kind of function that is calculating a value.
value_if_errorThe 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!.

IF ERROR

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.

IFERROR Function 2

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
valuethe value parameter contains the expression that is checked for the #N/A error. This parameter is required.
value_if_errorthe 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.

IFNA Error

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.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit