Excel Boolean logic: How to sift spreadsheet data using AND, OR, NOT, and XOR - grossgook1951

In Surpass, Boolean logic (a fancy name for a simple term that's either sure or false) is ace right smart to strain specific data or results from a large spreadsheet. Granted, there are opposite ways to search a spreadsheet, including Search functions and pivot tables. The rationality to bon along Boolean logic is because it's a method you can use in other applications, suchlike search engines and databases.

Boolean operators, which Excel calls formal functions, include AND, OR, Non, and a new function called XOR. These operators are used between search terms to narrow, expand, or keep out your results in databases, spreadsheets, search engines, or any situation where you're quest proper entropy. We'll manner of walking 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<200, 200>100) TRUE because both conditions are apodictic

OR – returns TRUE if at to the lowest degree one of the nominal conditions is true

Example: =Operating theater(100<200, 100>300) TRUE because one of the conditions is true

NOT – returns on-key if condition specific is NOT met (reverse logic)

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

XOR – also called Inside OR, returns true if either disputation (but non both) is lawful

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

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

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

A few many things to note:

• When you'rhenium searching for a range of results via Boolean operators, you define the range by what information technology's more than OR less than.
• Surpass 2022 allows a maximum of 255 arguments in a mateless logical function, but only if if the formula does not exceed 8,192 characters.

JD Sartain

01 Boolean logical operators characterized

Boolean operators may get sounding simple. When combined with other functions, however, such as IF statements, you can make over some complex formulas that produce rattling powerful results.

Boolean AND, IF-AND

When you'rhenium trying to find something that meets multiple criteria, AND is your operator. E.g.: One of the actors in George's bid broke his leg, so George needs another actor, in real time, with real specific skills and appearance. Ready to fit the costumes, the new guy must be 68 to 69 inches statuesque, moldiness count between 180 and 200 pounds, and essential be aged between 30 and 50.

If St. George's list of actors contained but 50 to 100 names, He could read the list and locate a replacement himself. But the Guild Actors database contains 20,000 records, so he necessarily a faster way to narrow the search.

For this query, you can use cardinal of the tailing three formulas. All trio formulas work and all are similar, except the AND statement only returns True or Unrealistic. The IF statements allow custom responses such as "Match" surgery "Qualified."

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

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

=AND(AND(C6>67,C6<70),AND(D6>179,D6<201),AND(E6>29,E6<51)) = Lawful

B.IF/AND statement using AND Boolean operators (with three conditions): returns Yes or No because the IF statement says: If this, and this, and this is true, and so answer Yes; else/differently, answer No.

=IF(AND(AND(C8>67,C8<70),AND(D8>179,D8<201),AND(E8>29,E8<51)),"Yes","No")

C.IF statement victimisation AND Boolean operators (with trine conditions): returns Yes or No.

In that case, if any of the AND statements are not met, the response volition return False and the multiplication (asterisk) result will be 0 (False). This format ofttimes appears when your syntax has an error and Surpass repairs it (afterwards asking you if you would ilk aid).

=IF(AND(C10>67,C10<70)*AND(D10>179,D10<201)*AND(E10>29,E10<51),"YES","Atomic number 102")

Note: Notice 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 to each one condition in the formula.

JD Sartain

02 Formulas that use AND, IF/AND, & IF statements.

Boolean Operating theater, AND-OR

The archetypal database look for returned 1100 actors. George III wants to narrow-mouthed the results further, so he queries those 1100 results for 2 very specific skills: This actor must speak fluent Italian or European nation AND have a song range of tenor Beaver State bass.

Use of goods and services the following formula for this query:

=Beaver State(Surgery(C9="Italian",C9="French"),AND(OR(D9="high-pitched",D9="base"))) = Correct

Remember, for the answer to personify actual, the actor must speak Italian Operating theatre French AND sing tenor OR bass.

Any incorrect selective information produces a Inharmonious 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 twin cells, including the opening and closing parentheses, in an effort to help you empathize the syntax of each shape in the pattern.

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 elderly friend Jack William Felton Russell just by typing his name, you'd get hundreds of hits for dogs and puppies, too. With the NOT operator, you can seek for "Jack Russell Non dogs NOT puppies" to remove the cuspid variable.

George I needs both background performers to trip the light fantastic and play a variety of instruments—but not the pianissimo, because pianists can't dance around, and not ballroom dancing, because he wants them to dancing with their instruments, not with anthropoid partners. George queries the database again and specifies NOT piano AND NOT ballroom terpsichore.

Call up, this is reverse system of logic, thusly Non piano and NOT dance hall equals Simulated because He doesn't want ballroom and he doesn't want piano. Think of FALSE as "No, non this mortal." Notice also that register 3 (Feyd-Rautha) says guitar and ballroom. Guitar is honorable, but dance palace is bad, so the response is FALSE because George doesn't want ballroom (even though guitar is okay). Synoptic situation for record 4 (Piter De Vries), pianissimo, waltz. Since solitary one is unobjectionable and not both, some are rejected.

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

=NOT(Surgery(C5="piano", D5="ballroom"))

JD Sartain

04 Normal that uses Non, Non-OR operators

Meet XOR, a.k.a. Exclusive OR

Just when you thought you had Boolean logic in the bag, Surpass 2022 introduced the modern operator XOR, which way Single OR. Think of information technology as a similar opposite of NOT: If one condition is unfeigned and one is false, XOR returns TRUE. If both conditions are true, or both conditions are false, XOR returns FALSE.

Use the favourable formula for this interrogation and then copy the database shown in envision 05 and try out with the results.

=XOR(C5="piano", D5="ballroom")

JD Sartain

05 Convention that uses XOR operator

Once you get comfortable with Mathematician operators, you have a original skill for determination specific records in a sea of cells. Advisable yet, you can branch out to use Boolean logic to to refine Internet searches, database searches, and much.

Source: https://www.pcworld.com/article/423639/excel-boolean-logic-how-to-sift-spreadsheet-data-using-and-or-not-and-xor.html

Posted by: grossgook1951.blogspot.com

Share this post