Excel 2007 – IF AND OR Functions Explained

Excel’s AND and OR formulas on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more powerful.

In this tutorial we’re going to take a look at the basics of the AND and OR functions and then put them to work with an IF Statement.  If you aren’t familiar with IF Statements, click here to read that tutorial first.

AND Function

The AND function belongs to the logic family of formulas, along with IF, OR and a few others.  It’s useful when you have multiple conditions that must be met.

In Excel language on its own the AND formula reads like this:

=AND(logical1,[logical2]….)

Now to translate into English:

=AND(is condition 1 true, AND condition 2 true (add more conditions if you want), if so do this, if not do that)

OR Function

The OR function is useful when you are happy if one, OR another condition is met.

In Excel language on its own the OR formula reads like this:

=OR(logical1,[logical2]….)

Now to translate into English:

=OR(is condition 1 true, OR condition 2 true (add more conditions if you want), if so do this, if neither are true do that)

See, I did say they weren’t very exciting, but let’s mix them up with IF and put AND and OR to work.

IF AND Formula

First let’s set the scene of our challenge for the IF, AND formula:

In our spreadsheet below we want to calculate a bonus to pay the children’s TV personalities listed.  The rules, as devised by my 4 year old son, are:

1)      If the TV personality is Popular AND

2)      If they earn less than $100k per year they get a 10% bonus (my 4 year old will write them an IOU, he’s good for it though).

In cell D2 we will enter our IF AND formula as follows:

In English first

=IF(Spider Man is Popular, AND he earns <$100k),  calculate his salary x 10%, if not put “Nil” in the cell)

Now in Excel’s language:

=IF(AND(B2=”Yes”,C2<100),C2x$H$1,”Nil”)

You’ll notice that the two conditions are typed in first, and then the outcomes are entered.  You can have more than two conditions; in fact you can have up to 30 by simply separating each condition with a comma (see warning below about going overboard with this though).

IF OR Formula

Again let’s set the scene of our challenge for the IF, OR formula:

The revised rules, as devised by my 4 year old son, are:

1)      If the TV personality is Popular OR

2)      If they earn less than $100k per year they get a 10% bonus.

In cell D2 we will enter our IF OR formula as follows:

In English first

=IF(Spider Man is Popular, OR he earns <$100k), calculate his salary x 10%, if not put “Nil” in the cell)

Now in Excel’s language:

=IF(OR(B2=”Yes”,C2<100),C2x$H$1,”Nil”)

Notice how a subtle change from the AND function to the OR function has a significant impact on the bonus figure.

Just like the AND function, you can have up to 30 OR conditions nested in the one formula, again just separate each condition with a comma.

Try other operators

You can set your conditions to test for specific text, as I have done in this example with B2=”Yes”, just put the text you want to check between inverted comas “   ”.

Alternatively you can test for a number and because the AND and OR functions belong to the logic family, you can employ different tests other than the less than (<) operator used in the examples above.

Other operators you could use are:

• =             Equal to
• >             Greater Than
• <=           Less than or equal to
• >=           Greater than or equal to
• <>           Less than or greater than

Warning: don’t go overboard with nesting IF, AND, and OR’s, as it will be painful to decipher if you or someone else ever needs to update the formula in months or years to come.

Note: these formulas work in all versions of Excel, however versions pre Excel 2007 are limited to 7 nested IF’s.

You can download the workbook used in this example to practice here.

For more on basic IF statements go here.

For more on Nested IF statements go here.

For more on Absolute References go here (they’re the formulas with the $ signs in the cell reference e.g. $H$1)

Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub.  Just type your name and email address in the box at the top left and click the ‘Get Free Access’ button for instant access to our free online training.

Share the knowledge with your friends and colleagues on Twitter, facebook etc. using the shortcuts below.

Got a clever way you use IF AND OR?  Let us know by leaving a comment.

Tagged as: excel formulas, excel if and or formulas