The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

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!


©2014 Blue Moose Technology, LLC

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

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