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.

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:

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:

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.