If And Or Explained

Microsoft training delivered by certified trainers, and a microsoft partner training company
excel training Excel – IF AND OR Functions Explained
logica
guides
Home
       

 

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 a formula that’s much more powerful.

In this tutorial we will take a look at the basics of AND and OR functions and then put them together with an IF Statement.

The AND Function
The AND function is a member of the logic family of formulas, along with IF, OR and a few others.  It’s useful when you have multiple conditions are to 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)

The 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)


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  TV personalities listed.  The rules are listed below.

  • If the TV personality is Popular AND
  • If they earn less than £100k per year they get a 10% bonus

ifandor1

  • 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, are as follows
  • If the TV personality is Popular OR
    If they earn less than £100k per year they get a 10% bonus.

    ifandor2

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

    In English first
    =IF(AL Murry 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: constraint should be used when nesting IF, AND, and OR’s, as it will be tedious to decipher if you or someone else needs to update the formula in months or years to come.
    Note: these formulas work in all versions of Excel, however versions prior to Excel 2007 are limited to 7 nested IF’s.

Excel can be fun try out some of its features Detailed on this Site

 

Mike Barrett (MCT) Senior Training Advisor