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
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
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
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.
- 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”))))
- 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.
- 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
- 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))))
- 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”)
- 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.