There are additional logical functions that you can use along with the IF function to add even more logical decision-making capability.
First, the AND function can be used to check if multiple conditions are true. Its syntax is:
AND(logical1, [logical2], [logical3] … )
logical1: condition to be tested
logical2, logical3, etc.: (optional) additional condition to be tested
The AND function returns a value of TRUE if all of the conditions specified in the arguments are true. If one argument is false, it returns a value of FALSE. Because it returns TRUE/FALSE, it can be used as the first argument of the IF function when you want to require multiple conditions be met. This will allow you to tell Excel what action to take if all conditions are met, and what action to take if one or more conditions are not met.
Our example worksheet contains a measurement and limits for the allowable tolerance:
We’ll combine the IF function with the AND function to display “Accept” in the box at right if the measurement is within the limits, and “Reject” if not. The formula will begin with =IF(AND( because the AND function is the first argument.
The AND function will check if two different criteria are true: Measurement ≥ 1.990 and Measurement ≤ 2.010. In this case, we’ll use cell references in our formula:
=IF(AND(B6>=C6,B6<=D6)
The first argument in the AND function checks that the measurement (B6) is greater than or equal to the lower limit (C6). (Recall that >= is the Excel form of the ≥ symbol.) The second argument checks that the measurement is less than or equal to the upper limit (D6).
If both of these criteria are true, the AND function will return a value of TRUE. Next, we’ll specify the value that the IF function should return if both conditions are true – the text string “Accept”:
=IF(AND(B6>=C6,B6<=D6),”Accept”,
Therefore, the function will display the text “Accept” if the measurement is between 1.990 and 2.010. If the measurement is outside those bounds, the AND function will return a value of FALSE. We’ll tell the IF function to return the string “Reject” in that case.
=IF(AND(B6>=C6,B6<=D6),”Accept”,”Reject”)
When you press Enter, the cell should display “Accept.” You can change the measurement at left to check that it will display “Reject” if the value is outside the limits.
The OR function can be used to obtain the same result. It has the same syntax as the AND function, but it will return a value of TRUE if one condition or the other is true. Therefore, to use the OR function for this same problem, we’ll specify to reject the part if either the measurement is below the lower limit or above the upper limit.
The first argument of the IF function will be the OR function: =IF(OR(
The OR function will have two arguments: one to check if the measurement (B6) is less than the lower limit (C6):
=IF(OR(B6<C6
The next will check if the measurement is above the upper limit (D6):
=IF(OR(B6<C6,B6>D6)
Because we’re using the OR function, it will return TRUE if either of those arguments is true. In that case, the part should be rejected because it’s out of tolerance – therefore, “Reject” will be the next argument:
=IF(OR(B6<C6,B6>D6),”Reject”,
If the OR function returns FALSE, it means that neither logical condition was true, and the measurement is within the limits. Therefore, we’ll accept the part:
=IF(OR(B6<C6,B6>D6),”Reject”,”Accept”)
The formula is working as expected: it is returning “Accept” because the measurement is within the limits. You can change the measurement to see that it will be rejected if it doesn’t meet the criteria.
