Pinterest Stumbleupon Whatsapp
Ads by Google

Logical operations are at the heart of programming and advanced online searching Become Better At Finding Stuff With Search Engines: Boolean Search Logic Explained Become Better At Finding Stuff With Search Engines: Boolean Search Logic Explained It only takes Google's thousands of servers half a second to query approximately 50 billion indexed pages, it takes you significantly longer to scan only the first page of search results. On top of that,... Read More , but did you know that they can also be used in Excel? Understanding the IF, NOT, AND, and OR functions can help you get from Excel newbie to power user. Here are the basics of each function, and an example of a way to use them together for maximum power.

The IF Function

IF, as you might imagine, evaluates whether a certain condition is true. This type of function is very basic in programming The Absolute Basics Of Programming For Beginners (Part 2) The Absolute Basics Of Programming For Beginners (Part 2) In part 2 of our absolute beginners guide to programming, I'll be covering the basics of functions, return values, loops and conditionals. Make sure you’ve read part 1 before tackling this, where I explained the... Read More , and usually returns a 1 if the condition is true, and 0 if it’s false. Fortunately, Excel’s IF is a bit more flexible than this. The Excel syntax looks like this:

=IF(logical_test, [value_if_true], [value_if_false])

As you can see, you need to specify a logical test (like A1<10, B16+30>C16, or D5=”alpha”) and two return values. If the condition is met, the cell in which you entered the syntax will display the value you entered for [value_if_true]. If not, it’ll display [value_if_false]. Let’s take a look at how you might use this in a real spreadsheet.

In our sample spreadsheet (created randomly with www.generatedata.com), we have a list of first names, last names, cities, states, and a number that represents an SAT score. First, we’ll try something simple: we’ll see which scores are higher than 1600. This is the syntax we’ll use:

=IF(E2>1600, "true", "false")

This will put “true” in the F column for every student who scored higher than 1600, and “false” for everyone else.

excel-if-syntax

Ads by Google

After using autofill 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office "Get that report to me, ASAP!" A core set of Microsoft Excel and Word tricks can make this task less stressful and help you whiz up a stylish report in no time. Read More to copy the formula into all of the cells in the column, every student in the table has a value for whether or not they scored over 1600.

excel-if-result

By itself, IF isn’t hugely useful; you can do most of the things that you’d do with it just by using filters or a pivot table How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it. Read More . But we’ll look in a moment at some ways to combine it with AND and OR to make it more powerful.

The NOT Function

NOT is the opposite of IF: it returns “TRUE” if the condition that you specify is not met. Here’s the syntax:

=NOT([logical_test])

All you need to do is put a logical test in the parentheses and Excel will tell you if it’s not true (it sounds a bit weird, but once you think about it, it makes sense). Because this is a pretty simple operator, and doesn’t see a whole lot of use, I won’t include a detailed example here, but it’s good to know about in case you come across a situation where it’s useful.

The AND Function

While the IF function checks to see if one condition has been met, the AND function checks to see if two have been met. If they have, the function returns TRUE, and if not, FALSE—you can’t choose custom return values like you can with IF; though there are ways to get around this by combining operators if you need to.

The AND function’s Excel syntax looks like this:

=AND([logical_test1], [logical_test2], [logical_test3]...)

The ellipsis at the end indicates that you can include as many different logical tests as you want—AND will only return TRUE if all of these tests are passed. Going back to our example spreadsheet, let’s say you want to find students who scored in the mid-range of the SAT, between 1,050 and 1,950. This is the syntax we’ll use:

=AND(E2>1050, E2<1950)

Here’s what it looks like in Excel:

excel-and-syntax

And here’s the result.

excel-and-result

As you can see, the function returns TRUE for any student who scored within the range we specified. If we wanted to get to an even more granular level, a third test could be added; to see mid-range scores in Wisconsin, for example, the syntax would look like this:

=AND(E2>1050, E2<1950, D2="Wisconsin")

The OR Function

As you might expect, the OR function also takes a number of logical test arguments, but will return TRUE if at least one of the tests comes up with a true value. The syntax is very similar to the AND function:

=OR([logical_test1], [logical_test2]...)

Again, the ellipsis indicates that you can use a number of logical tests, and if any one of them is true, the function will return TRUE. We’ll use this function to see which students live in Midwestern states:

=OR(D2="Minnesota", "D2="Wisconsin", D2="Iowa", D2="Nebraska", D2="Missouri", D2="North Dakota", D2="South Dakota", D2="Indiana", D2="Michigan", D2="Ohio", D2="Illinois", D2="Kansas")

Applied to the spreadsheet, you can see that every Midwestern student is now tagged with TRUE.

excel-or-result

In Excel 2013, there’s also an exclusive OR, which will only return TRUE if one and only one of the conditions is met. The XOR function is used in the same was as OR, but will return false if more than one of the conditions is met. It’s useful, but probably not worth upgrading Office Have Office 2010? Don't Buy Office 2013 - Here's Why Have Office 2010? Don't Buy Office 2013 - Here's Why I’m coming to the end of my trial period with Microsoft Office 2013. Over the weeks it has been a reasonably solid experience with one or two quirks causing me no end of frustration. But... Read More  for.

Combining IF, NOT, AND, and OR

As I mentioned previously, these functions do simple things that can often be done with data filtering. But by using them in conjunction, you can do much more powerful things; especially when you combine them with text-based functions, which I’ll be discussing in a future article.

Let’s say that a college recruiter has been assigned to call high-performing students in California, middle-performing students in Oregon, and low-performing students in Washington or Nevada. How might we implement that using these functions? We’ll have to nest a few of them:

=OR(AND(D2="California", E2>1950), AND(D2="Oregon", AND(E2>1050, E2<1950)), AND(OR(D2="Washington", D2="Nevada"), E2<1050))

That might look like a huge mess, but if you break it down, it’s pretty simple. The main function, OR, has three logical tests:

  • AND(D2="California", E2>1950)
  • AND(D2="Oregon", AND(E2>1050, E2<1950))
  • AND(OR(D2="Washington", D2="Nevada"), E2<1050)

The first AND argument contains two simple logical tests. The second AND argument has a nested AND function, so it will only return true if the student is from Oregon and has a score that’s above 1050 and below 1950. The third argument contains an OR function that creates a similar requirement. Let’s see what happens when we run this on our recruiting spreadsheet:

excel-if-and-or-result

All of the students that satisfy the condition we laid out above are marked with TRUE. In the image above, there are only two, and they’re both high-performing students from California.

Power up Excel

Going from spreadsheet novice to Excel expert takes a lot of time and practice, but understanding how to use logical operators will give you a head start and get you headed down the path to mastery. There’s a lot of computation power 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More hiding under the hood of Excel, and using these operators will help you access it.

How do you use these logical operators? What other Excel tips do you have for readers? Share your thoughts below!

  1. Raish
    March 16, 2015 at 10:19 pm

    Thanks for another great article.

    • Dann Albright
      March 23, 2015 at 7:56 am

      Glad you liked it!

Leave a Reply

Your email address will not be published. Required fields are marked *