How to use logical functions in excel if and or xor not

Excel logical functions make it easy to find the data you want, especially in huge spreadsheets.

Today’s Best Tech Deals

Picked by PCWorld’s Editors

Top Deals On Great Products

Picked by Techconnect’s Editors

In Excel, Boolean logic (a fancy name for a simple condition that’s either true or false) is one way to sift specific data or results from a large spreadsheet. Granted, there are other ways to search a spreadsheet, including Lookup functions and pivot tables. The reason to bone up on Boolean logic is because it’s a method you can use in other applications, like search engines and databases.

Boolean operators, which Excel calls logical functions, include AND, OR, NOT, and a new function called XOR. These operators are used between search terms to narrow, expand, or exclude your results in databases, spreadsheets, search engines, or any situation where you’re seeking specific information. We’ll walk you through all four.

Boolean basics

The simplest definition for each operator is this:

AND – returns TRUE if all conditions specified are true

Example: =AND (100 100) TRUE because both conditions are true

OR – returns TRUE if at least one of the specified conditions is true

Example: =OR(100 300) TRUE because one of the conditions is true

NOT – returns true if condition specified is NOT met (reverse logic)

Example: =NOT(100>500) TRUE because 100 is NOT greater than 500

XOR – also called Exclusive OR, returns true if either argument (but not both) is true

Examples: =XOR(1+1=2, 2-1=2) returns TRUE because one condition is true and one is false

=XOR(1+1=2, 2-1=1) returns FALSE because both conditions are true

=XOR(5+1=2, 5-1=2) returns FALSE because both conditions are false

A few more things to note:

• When you’re searching for a range of results via Boolean operators, you define the range by what it’s more than or less than.
• Excel 2013 allows a maximum of 255 arguments in a single logical function, but only if the formula does not exceed 8,192 characters.

JD Sartain

01 Boolean logical operators defined

Boolean operators may start out looking simple. When combined with other functions, however, such as IF statements, you can create some complex formulas that produce very powerful results.

Boolean AND, IF-AND

When you’re trying to find something that meets multiple criteria, AND is your operator. For example: One of the actors in George’s play broke his leg, so George needs another actor, immediately, with very specific skills and appearance. In order to fit the costumes, the new guy must be 68 to 69 inches tall, must weigh between 180 and 200 pounds, and must be aged between 30 and 50.

If George’s list of actors contained only 50 to 100 names, he could scan the list and locate a replacement himself. But the Guild Actors database contains 20,000 records, so he needs a faster way to narrow the search.

For this query, you can use one of the following three formulas. All three formulas work and all are similar, except the AND statement only returns True or False. The IF statements allow custom responses such as “Match” or “Qualified.”

Copy the database and formulas shown in figure 02 and experiment with the results.

A. AND statement using AND Boolean operators (with three conditions): returns true or false.

=AND(AND(C6>67,C6 179,D6 29,E6 67,C8 179,D8 29,E8 67,C10 179,D10 29,E10 JD Sartain

0 2 Formulas that use AND, IF/AND, & IF statements.

Boolean OR, AND-OR

The first database search returned 1100 actors. George wants to narrow the results further, so he queries those 1100 results for two very specific skills: This actor must speak fluent Italian or French AND have a vocal range of tenor or bass.

Use the following formula for this query:

Remember, for the answer to be true, the actor must speak Italian OR French AND sing tenor OR bass.

Any incorrect information produces a FALSE response.

Copy the database and formulas shown in figure 03 and experiment with the results. Once again, note how Excel color-codes the formulas to the matching cells, including the opening and closing parentheses, in an effort to help you understand the syntax of each condition in the formula.

03 Formula that uses OR, AND-OR operators

Boolean NOT, NOT-OR

The easiest way to explain the NOT operator is to compare it to an Internet search. If you searched online for your old friend Jack Russell just by typing his name, you’d get hundreds of hits for dogs and puppies, too. With the NOT operator, you can search for “Jack Russell NOT dogs NOT puppies” to remove the canine variable.

George needs some background performers to dance and play a variety of instruments—but not the piano, because pianists can’t dance around, and not ballroom dancing, because he wants them to dance with their instruments, not with human partners. George queries the database again and specifies NOT piano AND NOT ballroom dancing.

Remember, this is reverse logic, so NOT piano and NOT ballroom equals FALSE because he doesn’t want ballroom and he doesn’t want piano. Think of FALSE as “No, not this person.” Notice also that record 3 (Feyd-Rautha) says guitar and ballroom. Guitar is good, but ballroom is bad, so the response is FALSE because George doesn’t want ballroom (even though guitar is okay). Same situation for record 4 (Piter De Vries), piano, waltz. Since only one is acceptable and not both, both are rejected.

Use the following formula for this query, then copy the database shown in figure 04 and experiment with the results.

04 Formula that uses NOT, NOT-OR operators

Meet XOR, also known as Exclusive OR

Just when you thought you had Boolean logic in the bag, Excel 2013 introduced the new operator XOR, which means Exclusive OR. Think of it as a similar opposite of NOT: If one condition is true and one is false, XOR returns TRUE. If both conditions are true, or both conditions are false, XOR returns FALSE.

Use the following formula for this query and then copy the database shown in figure 05 and experiment with the results.

05 Formula that uses XOR operator

Once you get comfortable with Boolean operators, you have a new skill for finding specific records in a sea of cells. Better yet, you can branch out to use Boolean logic to to refine Internet searches, database searches, and more.

JD Sartain is a technology journalist from Boston. She writes the Max Productivity column for PCWorld, a monthly column for CIO, and regular feature articles for Network World.

To get detailed information about a function, click its name in the first column.

Note: Version markers indicate the version of Excel a function was introduced. These functions aren’t available in earlier versions. For example, a version marker of 2013 indicates that this function is available in Excel 2013 and all later versions.

Returns TRUE if all of its arguments are TRUE

Returns the logical value FALSE

Specifies a logical test to perform

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

IFNA function

Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression

IFS function

Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

Reverses the logic of its argument

Returns TRUE if any argument is TRUE

SWITCH function

Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

Returns the logical value TRUE

XOR function

Returns a logical exclusive OR of all arguments

Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

eBook includes PDF, ePub and Kindle version

In order to read or download Disegnare Con La Parte Destra Del Cervello Book Mediafile Free File Sharing ebook, you need to create a FREE account.

Download Now!

eBook includes PDF, ePub and Kindle version

We have made it easy for you to find a PDF Ebooks without any digging. And by having access to our ebooks online or by storing it on your computer, you have convenient answers with Excel Logical Formula . To get started finding Excel Logical Formula , you are right to find our website which has a comprehensive collection of manuals listed.
Our library is the biggest of these that have literally hundreds of thousands of different products represented.

Finally I get this ebook, thanks for all these Excel Logical Formula I can get now!

cooool I am so happy xD

I did not think that this would work, my best friend showed me this website, and it does! I get my most wanted eBook

wtf this great ebook for free?!

My friends are so mad that they do not know how I have all the high quality ebook which they do not!

It’s very easy to get quality ebooks 😉

so many fake sites. this is the first one which worked! Many thanks

wtffff i do not understand this!

Just select your click then download button, and complete an offer to start downloading the ebook. If there is a survey it only takes 5 minutes, try any survey which works for you.

lol it did not even take me 5 minutes at all! XD

eBook includes PDF, ePub and Kindle version

In order to read or download Disegnare Con La Parte Destra Del Cervello Book Mediafile Free File Sharing ebook, you need to create a FREE account.

Download Now!

eBook includes PDF, ePub and Kindle version

We have made it easy for you to find a PDF Ebooks without any digging. And by having access to our ebooks online or by storing it on your computer, you have convenient answers with Excel Logical Formula . To get started finding Excel Logical Formula , you are right to find our website which has a comprehensive collection of manuals listed.
Our library is the biggest of these that have literally hundreds of thousands of different products represented.

Finally I get this ebook, thanks for all these Excel Logical Formula I can get now!

cooool I am so happy xD

I did not think that this would work, my best friend showed me this website, and it does! I get my most wanted eBook

wtf this great ebook for free?!

My friends are so mad that they do not know how I have all the high quality ebook which they do not!

It’s very easy to get quality ebooks 😉

so many fake sites. this is the first one which worked! Many thanks

wtffff i do not understand this!

Just select your click then download button, and complete an offer to start downloading the ebook. If there is a survey it only takes 5 minutes, try any survey which works for you.

lol it did not even take me 5 minutes at all! XD

In this Article

This tutorial demonstrates how to use the Excel XOR Function in Excel to test if one and only one criteria is true.

XOR Function Description:

The XOR Function Checks whether one and only one condition is met. Returns TRUE or FALSE. If no conditions are met, or more than one condition is met, XOR returns FALSE. If only one condition is met, XOR returns TRUE.

To use the X OR Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

XOR function Syntax and inputs:

logical1 – Logical expressions. Example: A1 > 4.

What is the XOR Function?

XOR is one of Excel’s logical functions. It calculates something called an “exclusive OR” test (use the Excel OR Function if you need to use an inclusive OR).

What is an Exclusive OR?

The Exclusive OR is a little more complex than the standard OR. It takes a series of arguments, and evaluates them as either TRUE or FALSE. Then:

• If an odd number of arguments are TRUE, XOR returns TRUE
• If an even number of arguments are TRUE, XOR returns FALSE
• If no arguments are TRUE, XOR also returns FALSE (zero is an even number)

How to Use the XOR Function

With the Excel XOR Function, you supply a number of arguments. XOR first evaluates these arguments as TRUE or FALSE, and then performs the exclusive OR test on the results. See the example below:

XOR returns TRUE if an odd number of the arguments evaluate to TRUE, and FALSE if an even number of the arguments evaluate to TRUE.

Using XOR with Two Arguments

The most common usage of XOR by far, is when you only have two arguments in the function. This means that Excel will return TRUE if either one of the arguments is true, and FALSE if both, or none of the arguments is true. Here’s an example.

You’re the owner of a sports bar, and you want to give your staff a bonus if they sell over $800 of food, and $800 drinks in a given week. However, you’re feeling generous, so if they sell over $800 in either food or drink, but not both, you’ll give them a half bonus.

Here are the takings for last week:

In column E you’ve calculated the full bonus with Excel’s AND Function :

This returns TRUE if both food sales and drink sales are over $800.

And you can calculate the half bonus with the XOR function:

Note that the arguments in both functions are exactly the same, but XOR evaluates these arguments very differently to AND.

Using XOR with More than Two Arguments

Although using more than two arguments is less common in Excel, you can define up to 255 arguments in XOR.

However many you define, XOR will return an TRUE if an odd number evaluate to TRUE, and FALSE when an even number of them evaluate to TRUE.

To save time, you can supply a cell range here rather than separating the cell references with commas.

Comparing Text with XOR

Note that text comparisons are not case-sensitive. So the following formulas all produce the same result:

Also, XOR does not support wildcards. So if you used the following formula:

XOR will look for an exact match against the text string “Spiel*”.

Comparing Numbers

You have a range of comparison operators at your disposal when comparing numbers. These are:

If an expression in the XOR Function evaluates to a non-zero number, XOR will interpret that logical test as TRUE. If an expression is or evaluates to 0, XOR evaluates this to false. This is shown below:

Using XOR with IF

XOR is often used as part of a logical test in an IF statement. Essentially, IF enables you to customize the return value of the XOR test. So instead of just saying “TRUE” or “FALSE”, you can return anything – text, numbers, or even another formula.

Going back to our sports bar bonus example from earlier, you could use it like this:

IF first evaluates the XOR function. If it evaluates to TRUE, it then returns “Yes”. If it evaluates to FALSE, it returns “No”.

I’ve also added an IF statement to the “Full Bonus” formula in column E, which used the AND function rather than the XOR.

Combining IF with logical functions in this way can help make our data a little friendlier to the human eye.

Read more on the main page on the Excel IF Function .

XOR in Google Sheets

The X OR Function works exactly the same in Google Sheets as in Excel:

Additional Notes

Use the XOR Function to test if one and only one condition is met. Each condition must be a logical expression (ex: a1 >5), a reference to a cell containing TRUE or FALSE, or an array containing all logical values. If one and only one conditions are TRUE, the formula returns TRUE, otherwise it returns FALSE.

XOR is a logical function and is generally used in conjunction with another logical function, IF:
The IF Formula does one thing if a condition is TRUE and does another if a condition is FALSE. Often when using the OR Function you will “nest” it within an IF Function.

The XOR function defines exclusive OR. The XOR function in Excel provides a different result for different states. When there are two statements and one of them is TRUE, XOR gives a TRUE. Again if both are TRUE, the outcome will be FALSE. If none of the statements is TRUE, XOR will be FALSE. In this tutorial, we will learn how to use the XOR function in Excel.

Figure 1. Example of How to Use the XOR Function in Excel

Syntax

=XOR(Logical1,[Logical2],….)

• Logical1
  This value is required. This value can be an expression, constant, or reference. This could be either FALSE or TRUE.
• [Logical2…]|
  This value is optional. It can be a same expression, constant, or reference like Logical1.

The XOR Function in Excel

Exclusive OR is used in programming. People use this in coding.

The main purpose of the XOR function is to apply exclusive OR. This formula returns only TRUE or FALSE.

The XOR function is based on the OR function. The value comes in TRUE or FALSE in different logic. For example, suppose there are two statements. The XOR will return TRUE if one of the logic is TRUE. But if both of them are TRUE, the result will not be the same.

Setting Up Data

The following data set contains some logical values. They are in column A and B.

Figure 2. The Sample Data Set

To find the exclusive or using in column C using XOR:

• We need to select cell C2 .
• Assign the formula =XOR (A2, B2) to cell C2.
• Press Enter .
• Drag the formula from cells C2 to C6 using the fill handle in the bottom right to copy the formula to the entire column.

Figure 3. Example of Applying the XOR Function

This will show the result of the exclusive OR in column C.

Notes

• The outcomes of exclusive OR will be either TRUE or FALSE . There will be no other outcome.
• This function does not consider blank references.
• The values which are imputed in the function has to be logical. Otherwise, the outcome will show #VALUE!
• XOR will only work in Excel 2013 and later versions.
• When the TRUE logical has an odd number, XOR will return TRUE. Otherwise, it will will be FALSE.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Bottom Line: Learn how to use the XOR function in Excel to analyze attendance data.

Skill Level: Intermediate

Download the Excel File

The file that I work within the video can be found below. You can use it to follow along and reconstruct what I’m doing in the video.

Dunder Mifflin Attendance Report – Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report – Final.xlsx (166.6 KB)

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I’m planning to post a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Part of a Series

Just so you have some context, this post is the first of six in a series that resulted from an Excel Hash competition. Each year, Excel geeks like myself are tasked with building a worksheet that contains specific features. We compete to see whose solution is best. It’s a lot of fun.

My entry from this year is a salute to one of my favorite shows, The Office. It’s a dashboard that takes simple timestamp data and turns it into an attendance reporting tool that Dwight Schrute could proudly use to police his fellow coworkers.

If you’d like to see my entry for this year, watch this: Excel Hash: Attendance Report with Storm Clouds & Fireworks.

Here are the other posts in the series:

The XOR Function

Our first step in creating this attendance dashboard is to take all of the timestamp data and determine if each entry is an “In” or “Out” entry.

The logic is fairly straightforward. If there is one entry for any given employee, it means that they have come in to work and haven’t yet left. A second entry would indicate that they are now out of the office. So what we are really looking for, initially, is to know if there are an odd or an even amount of entries in the running list. That way, we can label the employee “In” or “Out” of the office.

That’s where the XOR function comes in. This function returns a TRUE result if there is an odd number of TRUE results in a range. Likewise, it returns FALSE if there is an even amount.

Note: For the purposes of our attendance tracker, the data should be sorted into chronological order.

Compatibility: The XOR function is available in Excel 2013 and later. If you have an older version you can use the ISODD(COUNTIF()) formula that I explain in the video and below.

Writing the XOR Function

To use the XOR function, simply type =XOR and Excel will prompt you to enter logical statements. You can also just feed it an array of true/false values.

For example in the video, the reference range that we want is (A2=A$2:A2). This tells Excel to return TRUE if the name in cell A2 is the same as any of the names found in the range from A2 to A2. Of course, that is only one cell, but as the formula is copied down to the cells below it, the range automatically expands. (The beginning of the range will always remain A2 because of the absolute reference (dollar sign), while the end of the range will change.)

The XOR function essentially evaluates how many times the selected cell matches the entries in the selected range. It returns TRUE when there is an odd amount and FALSE when even.

To make the report more understandable, we can wrap the function in an IF statement. If the function returns TRUE, the cell can read “In” and if FALSE, it can say “Out.”

So the final formula would be =IF(XOR(A2=A$2:A2),”In”,”Out”)

Using Table Range References

One quirk with Excel is that if you use the range references as I’ve outlined above (A$2:A2), it may not automatically extend when you add data to the bottom of your range. So an alternative is to use table range references.

Using INDEX, we can compare the first cell in a column with a range of cells from that same column. This accomplishes the same thing we did above with the regular range reference.

With our table reference, our range would be replaced with ([Employee]=INDEX([Employee],1):[@Employee]). This may look a bit gnarly if it’s the first time you’ve dealt with the INDEX function. However, using this option is much better if data is continually being added to your report.

Conclusion

If you don’t want to use the XOR function, you can use a combination of COUNTIF and ISODD as an alternative.

It would look like this: =ISODD(COUNTIF(A$2:A2,A2))

See the video above for details about how to construct that.

The next video in the series will take a look at calculating the duration of time that the employees were in the office.

I hope this post was helpful in explaining how the XOR function works. Please leave a comment below if you have questions about it.

The XOR function returns a logical Exclusive Or of all arguments.

Syntax

The XOR function syntax has the following arguments.

Logical1, logical2,… Logical 1 is required, subsequent logical values are optional. 1 to 254 conditions you want to test that can be either TRUE or FALSE, and can be logical values, arrays, or references.

Remarks

The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.

If an array or reference argument contains text or empty cells, those values are ignored.

If the specified range contains no logical values, XOR returns the #VALUE! error value.

You can use an XOR array formula to see if a value occurs in an array.

Note: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter.

Because all test results evaluate to FALSE, FALSE is returned. At least one of the test results must evaluate to TRUE to return TRUE.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is True or False.

=IF(Something is True, then do something, otherwise do something else)

But what if you need to test multiple conditions, where let’s say all conditions need to be True or False ( AND), or only one condition needs to be True or False ( OR), or if you want to check if a condition does NOT meet your criteria? All 3 functions can be used on their own, but it’s much more common to see them paired with IF functions.

Use the IF function along with AND, OR and NOT to perform multiple evaluations if conditions are True or False.

IF(AND()) – IF(AND(logical1, [logical2], . ), value_if_true, [value_if_false]))

IF(OR()) – IF(OR(logical1, [logical2], . ), value_if_true, [value_if_false]))

IF(NOT()) – IF(NOT(logical1), value_if_true, [value_if_false]))

The condition you want to test.

The value that you want returned if the result of logical_test is TRUE.

The value that you want returned if the result of logical_test is FALSE.

Here are overviews of how to structure AND, OR and NOT functions individually. When you combine each one of them with an IF statement, they read like this:

AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)

OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)

NOT – =IF(NOT(Something is True), Value if True, Value if False)

Examples

Following are examples of some common nested IF(AND()), IF(OR()) and IF(NOT()) statements. The AND and OR functions can support up to 255 individual conditions, but it’s not good practice to use more than a few because complex, nested formulas can get very difficult to build, test and maintain. The NOT function only takes one condition.

Here are the formulas spelled out according to their logic:

=IF(AND(A2>0,B2 0,B4 50),TRUE,FALSE)

IF A6 (25) is NOT greater than 50, then return TRUE, otherwise return FALSE. In this case 25 is not greater than 50, so the formula returns TRUE.

IF A7 (“Blue”) is NOT equal to “Red”, then return TRUE, otherwise return FALSE.

Note that all of the examples have a closing parenthesis after their respective conditions are entered. The remaining True/False arguments are then left as part of the outer IF statement. You can also substitute Text or Numeric values for the TRUE/FALSE values to be returned in the examples.

Here are some examples of using AND, OR and NOT to evaluate dates.

Here are the formulas spelled out according to their logic:

IF A2 is greater than B2, return TRUE, otherwise return FALSE. 03/12/14 is greater than 01/01/14, so the formula returns TRUE.

=IF(AND(A3>B2,A3 B2,A4 B2),TRUE,FALSE)

IF A5 is not greater than B2, then return TRUE, otherwise return FALSE. In this case, A5 is greater than B2, so the formula returns FALSE.

Using AND, OR and NOT with Conditional Formatting

You can also use AND, OR and NOT to set Conditional Formatting criteria with the formula option. When you do this you can omit the IF function and use AND, OR and NOT on their own.

From the Home tab, click Conditional Formatting > New Rule. Next, select the “ Use a formula to determine which cells to format” option, enter your formula and apply the format of your choice.

Edit Rule dialog showing the Formula method” xmlns:AntiXSS=”urn:AntiXSSExtensions” />

Using the earlier Dates example, here is what the formulas would be.

If A2 is greater than B2, format the cell, otherwise do nothing.

=AND(A3>B2,A3 B2,A4 B2)

If A5 is NOT greater than B2, format the cell, otherwise do nothing. In this case A5 is greater than B2, so the result will return FALSE. If you were to change the formula to =NOT(B2>A5) it would return TRUE and the cell would be formatted.

Note: A common error is to enter your formula into Conditional Formatting without the equals sign (=). If you do this you’ll see that the Conditional Formatting dialog will add the equals sign and quotes to the formula – =”OR(A4>B2,A4

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.