Microsoft Excel Function Combinations - IF, AND, OR

Using OR, AND & IF

We can combine both an OR and an AND function within an IF statement. By doing this, we can create some interesting conditional formulas to get even more out of our data.

In Figure 1, we’ve added an additional column with a weekday to illustrate how you could use both an AND and OR function within an IF statement to really narrow down the type of value you get as a result:

Figure 1

AND, OR with IF ...

Our formula for the calculations in column D is:

=IF(OR(AND(A1>300,B1="Blue"),AND(A1<300,C1="Monday")),
"Correct","Wrong")

What this formula is saying in plain English is, “If A1 is over 300 and B1 is “Blue”, return ‘Correct’ – OR – if A1 is less than 300 and C1 is “Monday”, return ‘Correct’. Otherwise, it’s ‘Wrong’!”

So as you can see from this example, using both an AND and OR within an IF statement can really help you create some very pointed formulas to get exactly what you need out of your data from a conditional standpoint.

Curious as to what this would look like if it was just IF formulas?

=IF(A1>300,IF(B1="Blue","Correct","Wrong"),IF(C1="Monday",
"Correct","Wrong"))

Using OR and AND only has 5 less characters than using just IF statements, but the space you’re saving isn’t the concern here. What is of concern is the readability and flexibility of your formula.

By far, using AND and OR in conjunction with IF is a more desirable way to write your statement because if you need to add a condition or remove a condition, you don’t need to restructure or re-write the entire formula to do it. Plus, it’s just much easier to understand!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Excel Function Combinations - IF, AND, OR