Logical Functions in Excel Part II – AND and OR

Posted on November 16 2011 09:44 PM by John Atten in Excel Basics, Microsoft   ||   Comments (0)

Having represented this site as a technology blog with a focus on programming and software development, I find myself starting off with a tutorial on Excel basics instead. Somehow, I thought it was going to be more glamorous than this . . . This is driven by my need to prepare a training for my staff at work, and in keeping with the DRY principle (Don't Repeat Yourself) I am killing two birds with one stone. Anyone hoping to read programming stuff is just going to have to wait . . .

 

 

 

 

If you do not have Microsoft Excel on your machine, or if you are using an older version (I use Excel 2010 throughout this series) you might consider purchasing a copy. You can find many deals on-line, or even support my efforts with this site by using the links above to buy directly from Microsoft. I STRONGLY recommend against purchasing software from Craigslist or EBay. There is significant risk of purchasing illegal or pirated software from those sites.

Picking up where we left off . . .

In a we examined the logical IF function, and the basics of its use within MS Excel. The close cousins of logical IF are logical AND and Logical OR. This group of functions is one of the more powerful features of Excel, but getting used to the absolute way the machine handles logical processing can be frustrating at first if you are not accustomed to the special meaning associated with IF, AND, and OR in the binary world of computing.

A little background for the uninitiatedblack_beauty_custom_les_paul_gibson_HQ

Logical functions such as employed by MS Excel and indeed, most computer processes, attach a slightly different, and more stringent meaning to IF, AND, and OR than you may be accustomed to in everyday language. Your computer, at the deepest level, only really understands two values: 1 and 0. These can be metaphorically expressed as "On" and "Off", or "Yes" and "No" or "True" and "False". However, in the processor, they are reduced to ones and zeros.

We will focus on the TRUE/FALSE paradigm here.

Logical functions rely on expressions which return a value of either TRUE or FALSE. There is no in-between, because your computer (and MS Excel) do not understand "Kind of" or "Sometimes" or "Maybe".

The Mathematical expression 2 > 1 returns a value of TRUE. The expression 2 < 1 will always return a value of FALSE. As we learned previously, a logical IF function evaluates an expression (or condition) and returns a specific value if the expression is TRUE, and a different value if the expression is FALSE. We stated this in plain language like so:


If Some Conditionis TRUE Then Do something. Otherwise Do Something Else.


But what if more than one condition needs to be met in order to Do Something instead of Doing Something Else?This is where we have to look at compound logical statements; hence, AND and OR.

This AND That - the logic of buying guitars

Logical AND evaluates two or more expressions, and only returns a value of TRUE if ALL of the expressions being evaluated ALSO return TRUE. For example, I went shopping for a new guitar a couple years ago, and I had some very specific requirements for what I wanted. I was going to buy a black Gibson Les Paul Custom with gold hardware. In evaluating guitars for potential purchase, I processed my decision like this (sub-consciously, because I am not THAT big a geek) :


The guitar must be a Gibson Les Paul

AND

The Guitar must be black

AND

The guitar must have gold hardware.


Now, there was actually more to my decision on this matter, involving price, and the condition of the candidate guitar (a man must have his toys . . .). But you can see that all three of the above conditions needed to be TRUE in order that I might consider purchasing a particular guitar.

I could re-write this like so:

IF [Guitar = Gibson Les Paul AND Color = Black AND Hardware = gold] then Buy it Otherwise Keep Looking

In this expression, each of the individual conditions within the square brackets must all be true in order for the enclosing IF function to return true. If even one of the three expressions within the square brackets is NOT true, then the IF function returns FALSE, and Johnny does NOT get to buy the guitar.

MS Excel provides the following syntax for the AND function:

=AND(condition1, condition2, condition3,  . . . etc.)

If all of the nested conditions are TRUE, then the AND function will return TRUE as well. We can try this out right now (remember to always begin your formula with an equals sign!):

AND Function 1 Before Enter

 

 

Then we hit enter, and voila:

AND Function 1 After Enter

 

 

Now, in order to determine which of these classic guitars fit my criteria, I can simply copy the formula into the other cells in the "Buy it?" column:

AND Function 1 After Paste

 

 

As we can see, while there are several very attractive guitars here, there is only one which meets all three of my criteria for a purchase, as indicated by our AND function.

Les Paul OR Stratocaster? Life is full of difficult choices . . .

FenderStrat_04 SmallNow I have a problem, however. While I was looking at all these guitars, I remembered that I also really love the tone and playability of the Fender Stratocaster as well. In fact, if just the right one came along while I am out and about shopping for a Les Paul, I might just snap it up. After all, a man has GOT to have some diversity of tone, right? If I were to run into the right Les Paul OR the right Stratocaster, I will make a purchase. In a Strat, I am looking for the same things as with the Les Paul. Black, with gold hardware (OK, not really - gold hardware on a black Strat would just be . . . not right. but I need to keep the example simple here). I have now introduced an OR into my equation.

Logical or evaluates two or more expressions, and returns TRUE if one or more of the expressions return true. In other words, unlike AND, which requires ALL conditions to be true in order for the AND function itself to be true, OR requires only that at least ONE of the nested conditions are true. In my example above, if the guitar under consideration is a Gibson Les Paul OR a Fender Stratocaster, my first condition has been met. The OTHER two conditions (black, AND gold hardware) BOTH have to be true in order for me to buy the guitar. We can write this as follows:

IF { [guitar = "Gibson Les Paul" OR "Fender Stratocaster"] AND color = "Black" AND "Hardware = "gold"] } Then Buy It! Otherwise, Keep Looking.

Notice that we now have a more complex expression. We have an OR (within the square brackets) which must be true as one member of a set of three conditions (within the "curly braces") that ALL must be true.

The syntax for logical OR in MS Excel is similar to that of AND:

=OR(condition1, condition2, condition3, . . . etc.)

However, in our case, we are nesting an OR within and AND, so we would write it like THIS in Excel:

OR Function 1 Before Enter

 

Note in the above example that the OR is nested within the and. The placement of your parentheses is important here. Forgetting to close the paren on the OR statement will really screw things up! If we hit enter, and then copy the formula to the other cells in the "Buy it?" column:

OR Function 1 After Enter

There are now two guitars which meet our criteria.

It's, um, complicated

It is possible to perform stunning leaps of logical manipulation by cleverly employing IF, AND, and OR within your spreadsheets. It takes practice though, and when you need to employ nested logical statements it often pays to map things out beforehand in pseudo-logic like we did above. Play with it, and then the nest time you find yourself wishing you could perform one calculation if this AND that OR some other thing met certain conditions, you will have a running start.

 

Posted on November 16 2011 09:44 PM by John Atten     

Comments (0)

About the author

My name is John Atten, and my "handle" on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, JavaScript/Node, and databases of many flavors. Actively learning always. I dig web development. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

jatten at typecastexception dot com

Web Hosting by