Logical operations are at the heart of programming and advanced online searching, 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, 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.

After using autofill 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.

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. 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:

And here's the 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.

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 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:

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 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!