Using the IF AND OR Functions in Microsoft Excel 2010

With the IF, AND, andOR functions, we can check multiple condition at the same time.
Combining all of them we can test multiple conditions in a single formula.

A simple IF….Then Statement is where there is a True or False condition. In some situations, you need to write a Nested IF statement. These may look a bit complicated but they are simple once you understand the whole concept.

A Nested IF statement will look like this:

=IF(“if this condition stated here is true”, then enter “this value, else if(“if this condition stated here is true”, then enter “this value, else enter “this value”))

IF: Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Syntax =IF(logical_test,value_if_true,value_if_false)

 

Parameters:

Logical test is the condition or a value that you want to test.
value_if_true is optional. It is the value that is returned if the condition is TRUE.
value_if_false is optional. It is the value that is returned if the condition is FALSE.

 

AND: Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE

Syntax =AND(logical1,logical2,…)

Even if one argument in the list of arguments is false, the function will return False.

 

OR: Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE

Syntax =OR(logical1,logical2,…)

Even if one argument in the list of arguments is true and the rest are false, the function will return True.

 

Let’s take an example:

    • We have Months in column A & our requirement is to show Months as Quarter number.

 

img1

 

    • Criteria & Result are shown in column D& E.
    • If cell A4 contains Jan, Feb or Mar, theresult should show as 1st Quarter
    • If cell A4 contains Apr, May or June, theresult should show as 2nd Quarter & so on.
    • OR Function will check whether any or more than one condition have met and it will return TRUE. If all the conditions are not met, then OR will return FALSE.
    • Formula in cell B4=IF(OR(A4=”Jan”,A4=”Feb”,A4=”Mar”,),”1st Quarter”,IF(OR(A4=”Apr”,A4=”May”,A4=”Jun”,),”2nd Quarter”,IF(OR(A4=”Jul”,A4=”Aug”,A4=”Sep”,),”3rd Quarter”,IF(OR(A4=”Oct”,A4=”Nov”,A4=”Dec”,),”4th Quarter”))))

 

img2

 

  • The If condition will check if the value in cell A4 has “Jan, Feb or Mar”
  • If the condition is found to be true then it will immediately return “1st Quarter” as we can see in B4, B5 and B6.
  • Cell A7 contains “Apr”, so the first condition will return False and the formula will jump to the next condition & check if it is True or False and on this basis it will return the result as already mentioned in column D & E.
  • In case of Nested If, If Function will check logical test 1 if found to be Falsethen jump to logical test 2 & so on till the result is found to be True

 

Now lets review the IF and AND formulae.

Example 2:

    • We have production data per employee and we need to calculate the incentive based on the production figure. If the Production in column B is meeting the given conditions then the Incentive would be calculated as per the parameters shown in the below figure

 

img3

 

    • In column D, the formula should calculate the incentive.
    • If there is any Defective Production then there is no Incentive.
    • If there is “Yes” in column C then the Employee in the corresponding column will not be entitled to get any Incentive.
    • If Defective Production is “No”, then the formula will calculate the incentive based on the criteria.
    • Formula in cell D5 is =IF(C5=”Yes”,0,IF(B5>=20000,B5*30%,IF(B5>=15000,B5*20%,IF(B5>=10000,B5*10%,0))))

 

img4

 

    • Formula in cell D5=IF(AND(B5>=20000,C5=”No”),B5*30%,IF(AND(B5>=15000,C5=”No”),B5*20%,IF(AND(B5>=10000,C5=”No”)
      ,B5*10%,0)))

 

img5

 

  • This formula will use the AND operator which will return TRUE if all the conditions evaluate to TRUE.
  • If the Defective Production column has yes in it, then the function will directly return the “value if false” value which is 0.
  • Here we are checking for two conditions with the AND operator i.e. cell C5 is equal to “No” &the value in column B.
  • Depending on the Criteria level it will return the Incentive.
  • Cell C6 is “No” &the Production in cell B7 is meeting the second criteria, hence the incentive will get calculated @ 10% (14,000 x 10% = 1,400)

Similarly, the other values in the column will be calculated.

Users are saying about us...

  1. I have a row of calculations for an end of life budget. In essence if I have a motor worth 500 and its expected life is 7 years I want the sheet to allocate the cost at the 7 year mark. I am using an “if” formula (fairly simple) that essentially say If Install date + life expect =< a year in a row then the true statement is 1 and the false statement is 0. My issue is when I copy this function across a row EVERY field past the seven year date comes up s true (as is expected). What I am looking for is a formula that will 'reset the counter to the 0 date when the motor is replaced – this make sense???

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube