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 previous post 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 uninitiated
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 Condition is 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!):
Then we hit enter, and voila:
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:
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 . . .
Now 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:
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:
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.
Comments