If you've been following along on our proverbial Hogwarts for budding Excel wizardry, you would know that we recently crossed an important Rubicon upon which all Excel, and indeed all computer programming languages are built: The mighty IF statement. Remember, the IF statement allows the wielder to fork reality at their whim, to bend the code on a lark, to be the Franklin Richards of the spreadsheet just by asking the computer a simple true/false question. Considering the gravity of this momentous revelation, I implore you to read the previous statement to join us in enlightenment.

As we've already explored, all code, no matter how it's written, is built upon a foundation of ones and zeros, sequenced in an infinite number of ways to obtain an infinite number of results, and the key to harnessing this power lies within the IF statement... kind of. Much like one can write a sentence with two letters (GO!) or write an entire paragraph using the word "buffalo" doesn't necessarily mean that the next Nobel Prize in Literature will be won with the groundbreaking poem GO BUFFALO! (or at least, I hope not). That said, while the IF statement is the foundation on which all code is built, it has friends in the form of OR and AND.

To put it simply, the IF statement checks to see if a given statement is true or false, but rarely do you want to check if one number is equal to another. Sometimes, you want to get a bit more complex: enter the AND and OR statements: the proverbial cherry and whipped cream atop the Sunday.

The thing to always keep in mind is that everything always comes back to a true/false IFto check if one number is equal to another merely statement, the AND and OR statements only add more things to the mix for Excel to verify.

Let's get one thing out of the way off the bat: while every programming language contains IF, AND, and OR, the way you write out these statements (or perhaps, questions?) in Excel's front-end (which is a fancy way to say "the thing that you see) is downright wacky. In the majority of programming languages, one would write an IF statement with an OR or an AND like you would a sentence:

**IF the age of Jim > The age of Bob
AND Bob is old
(then)
"Print Jim is Old, older than Bob"

In most programming languages, you would write that code almost exactly as I wrote it (note that almost all programming languages imply the "then"... including Excel). However, Excel likes to be different and forces you to essentially write the same code in an ass-backward way, kind of like this:

IF(AND(Jim>Bob,Bob = old),"Jim is old, older than bob"

You'll notice immediately that you'll need an AND before you write your IF statement, which is the opposite of how sentences (or even thinking in general) work. On the plus side the way AND works in Excel is that it lets you keep adding things to check after each comma, think of each comma within the AND statement as adding another "and" to the sentence. In other words, you only need to write AND once for each IF you have.

To put things less esoterically, here are the nuts and bolts behind "AND":

First, you have the IF statement, which checks to see if a given value is true or false. The example above is of Jim's age. If you were checking Jim's age against Bob's age, and let's say Jim's age is written in cell A1 and Bob's age is written in cell B1, the IF would be written like this:

**=IF(A1 > B1, "Jim is old", "Jim is young")

You're seeing if Jim is older than Bob, and then you're given two choices:

  1. If Jim is older than Bob, Jim is old
  2. If Jim is not older than Bob, Jim is young.

Naturally, though, one's age is not measured in Bobs, and normally, you can't tell if any Jim off the street is old just by their proximity to a Bob...so enter the AND.

In Excel, and it is own formula (you can technically write =AND(1,2) and it'll return "true" because 1 and 2 are, in fact, numbers) which checks to see if two (or more) arguments within its parenthesis are true, and it'll return "true" if so. So, let's say You have Jim's age in cell A1, Bob's age in Cell B1, and the phrase "this example is falling apart" in cell C1, and Jim can only be considered "old" if this example is falling apart; you'd write it like this:

**=IF(AND(A1>B1,C1= "this example is falling apart"),"Jim is Old!","Jim is not old!")

So let's look at this from left to right in English: You're checking to see if Jim is old (the IF statement). You know that Jim can only be considered old if he is older than Bob (cell B1) AND if this example is falling apart (C1), if all of these things are true, then Jim is, in fact, old, or else, he is not old.

Before we close out for the night, here is a quick PROTIP!: the ass-backward way Excel forces you to write AND statements have an upside: you only need to write AND once for each IF statement you include (and you better believe you can use more than one...tune in next time!). In other words, you can keep adding things to check the truth by adding commas after the AND(. Think of each comma you write after the second parenthesis but before the 3rd as adding another AND to the mix... just keep one thing in mind: if only one of those is false, the whole house of cards falls apart (which may be what you're trying to accomplish!)

In summary, in this lesson we went over:

  • The massive impact of the IF statement.
  • How you can get jiggy with IFs by using AND.
  • How to spice up your IFs with ANDs.
  • How to tell if Jim is indeed old.

Next time, I'll push this ridiculous example to infinity and beyond when we make like a bird and Nest.

-Snacks