Excel Tutorial
Excel Conditional Formulas
Creating a conditional formula that results in a logical value (TRUE or FALSE)
For conditional formulas, we use AND, OR, and NOT functions, and operators as is shown in the examples given below:
Example 1
=AND(B2>B3, B2<B4)
This formula verifies whether B2 (20) is greater than B3 (14) and less than B4 (12)? AND function is used if we want to test whether both conditions meet. As both conditions do not meet, the result is (FALSE).
Example 2
=OR(B2>B3, B2<B4)
This formula verifies whether B2 (20) is greater than B3 (14) or less than B4 (12)? OR function is used if we want to test if one of the condition meet. As the first condition is met, the result is TRUE.
Example 3
=NOT(B2+B3=34)
This formula verifies whether B2 (20) plus B3 (14) is not equal to 34?
As the B2 (20) plus B3 (14) is equal to 34, the result is FALSE.
Example 4
=NOT(B5="Gadgets")
This formula verifies whether B5 is not equal to Widgets?
As B5, is not equal to Gadgets, the result is TRUE.
Example 5
=OR(B5<>"Widgets",B6="Gadgets")
This formula verifies whether B5 is not equal to "Widgets" or B6 is equal to Gadgets?
As B5 is equal to "Widgets". The first condition is not met.
But as B6 is equal to "Gadgets", the second conditions ismet.
As one of the two conditions is met, the result is TRUE
Creating a conditional formula that results in another calculation
or in values other than TRUE or FALSE
To do this task, we use the IF, AND, and OR functions as shown in the examples below:
Example 1
=IF(A2=15, "OK", "Not OK")
This formula checks if the value in cell A2 equals 15, then return "OK".
As the condition is met the result is (OK)
Example 2
=IF(A2=15, "OK", "Not OK")
This formula checks the condition if the value in cell A2 is equal to 15.
If condition is met then return "OK".If the condition is not met returns "Not OK".
As the condition is met, the result is "OK".
Example 3
=IF(NOT(A2<=15), "OK", "Not OK")
This formula checks the condition if the value in cell A2 is not less than or equal to 15.
If the condition is met then return "OK".
If the condition is not met then return "Not OK".
As the condition is not met, the result is "Not OK"
Example 4
=IF(A5<>"Packs", "OK", "Not OK")
This formula checks the condition if the value in cell A5 is not equal to "Packs".
If the condition is met then return "OK".
If the condition is not met then return "Not OK".
As the condition is not met, the result is "Not OK"
Example 5
=IF(AND(A2>A3, A2<A4), "OK", "Not OK")
This formula checks the conditions if 15 is greater than 9 and if 15 is less than 8.
If both conditions are met, then return "OK".
If one or both conditions do not meet, returns "Not OK".
As first condition is met but second condition is not met, the result is "Not OK"
Example 6
=IF(AND(A2<>A3, A2<>A4), "OK", "Not OK")
This formula checks the if both the conditions that 15 is not equal to 9 and 15 is not equal to 8 are met.
If both conditions are met, then return "OK".
If one or both conditions do not meet, returns "Not OK".
As both conditions are met, the result is "OK"
Example 7
=IF(OR(A2>A3, A2<A4), "OK", "Not OK")
This formula checks the if one of the two conditions that 15 is greater than 9 or less than 8 is met.
If one or both conditions are met, then return "OK".
If both conditions do not meet, returns "Not OK".
As one of the two conditions is met, the result is "OK"
Example 8
=IF(OR(A5<>"Packs", A6<>"Goods"), "OK", "Not OK")
This formula checks the if one of the two conditions that value in cellA5 is not equal to "Packs" or that value in cellA6 is not equal to "Goods"are met.
If one or both conditions are met, then return "OK".
If both conditions do not meet, returns "Not OK".
As both conditions are not met, the result is "Not OK"
Example 9
=IF(OR(A2<>A3, A2<>A4), "OK", "Not OK")
This formula checks the if one of the two conditions that 15 is not equal to 9 or 15 is not equal to 8.
If one or both conditions are met, then return "OK".
If both conditions do not meet, returns "Not OK".
As both conditions are met, the result is "OK"