- Create an Iterative List in Excel Without Any Real Coding
- Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel
- Active-X Gonna Give it to Ya
- Your Invitation to the Excel Pivot Party
- Slicing Excel: The Way of The Knife
- Yes...If(And)): Excel’s Mighty “IF” Statement
- Getting Iffy With it: Conditional Statements in Excel
- Taking Out the Trash: Dirty Data in Excel
- Dealing with Dirty Data in Excel
- Doing the Excel Data Dance
- Adventures in Excel: Power to the Pivot
- The Art of Excel Pivot Tables
- The One Excel Formula to Rule Them All
- Excel Showdown: Index Match vs. The Lookups
- How I Stopped Worrying and Learned to Love the Formula
- Excel Data Manipulation: a Guide For Everyone
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 in order 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 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 is going to be won with the groundbreaking poem GO BUFFALO! (or at least, I hope not). With that being 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 merely check if one number is equal to another, sometimes you want to get a little bit more complex: enter the AND and OR statements: the proverbial cherry and whip cream atop the Sunday.
The thing to always keep in mind is that everything always comes back to a true/false IF 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 frontend (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 the vast majority of programming languages, you would write out 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 backwards way, kind of like this:
IF(AND(Jim>Bob,Bob = old),"Jim is old, older than bob"
You'll notice right away that you'll need an AND before you write your IF statement, which is kind of the opposite of how sentences (or even thinking in general) works. 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. In the example above, its 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")
Basically, you're seeing if Jim is older than Bob, and then you're given two choices: If Jim is older than Bob, Jim is old, 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 is it's 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 it's 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, a quick PROTIP!: the ass-backwards way Excel forces you to write AND statements has 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 of just 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.