The IF function allows you to make a discursive comparison betwixt a value and what you expect by testing for a condition and returning a result if that condition is True operating room False.
-
=IF(Something is True, then do something, other than do something else)
But what if you need to test multiple conditions, where let's say all conditions need to be True or False (AND), or only one condition needs to be True or False (OR), Beaver State if you deprivation to check if a condition does Non meet your criteria? Whol 3 functions can be used happening their own, but it's much more grassroots to see them paired with IF functions.
Use the IF affair on with AND, OR and NOT to perform multiplex evaluations if conditions are True Oregon False.
Sentence structure
-
IF(AND()) - IF(AND(logical1, [logical2], ...), value_if_true, [value_if_false]))
-
IF(OR()) - IF(OR(logical1, [logical2], ...), value_if_true, [value_if_false]))
-
IF(Non()) - IF(NOT(logical1), value_if_true, [value_if_false]))
| Argument name | Description | |
| logical_test (required) | The experimental condition you want to test. | |
| value_if_true (required) | The value that you want returned if the result of logical_test is TRUE. | |
| value_if_false (optional) | The value that you want returned if the result of logical_test is FALSE. | |
Here are overviews of how to complex body part AND, OR and NOT functions separately. When you conflate each one of them with an IF statement, they take like this:
-
AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)
-
OR – =IF(Beaver State(Something is True, Something else is True), Prise if True, Value if False)
-
Non – =IF(Non(Something is True), Value if True, Value if False)
Examples
Following are examples of some common nested IF(AND()), IF(OR()) and IF(Non()) statements. The AND and OR functions can support up to 255 individual conditions, but it's non good practice to use more than a couple of because tangled, nested formulas can cause very difficult to anatomy, examination and maintain. The NOT function but takes one stipulation.
Here are the formulas spelled out according to their system of logic:
| Normal | Description |
|---|---|
| =IF(AND(A2>0,B2<100),TRUE, FALSE) | IF A2 (25) is greater than 0, AND B2 (75) is to a lesser degree 100, and so return TRUE, otherwise return FALSE. In this case both conditions are true, so TRUE is returned. |
| =IF(AND(A3="Red",B3="Green"),TRUE,FALSE) | If A3 ("Blue") = "Red", AND B3 ("Green") equals "Green" then return Avowedly, otherwise return Inconstant. In this case single the low condition is sure, so Faithlessly is returned. |
| =IF(Surgery(A4>0,B4<50),TRUE, FALSE) | IF A4 (25) is greater than 0, OR B4 (75) is to a lesser degree 50, then retrovert Even, otherwise return FALSE. In that display case, only the forward condition is Apodictic, but since OR only requires one argument to be true the formula returns TRUE. |
| =IF(OR(A5="Red",B5="Green"),Reliable,FALSE) | IF A5 ("Blue") equals "Red", OR B5 ("Green") equals "Super" then return TRUE, other than riposte FALSE. In this case, the second argument is True, and so the formula returns TRUE. |
| =IF(NOT(A6>50),Literal,FALSE) | IF A6 (25) is Non greater than 50, then return TRUE, otherwise return FALSE. In this case 25 is not greater than 50, so the formula returns TRUE. |
| =IF(Non(A7="Red"),Genuine,FALSE) | IF A7 ("Blue") is NOT up to "Red", past return TRUE, otherwise return Inconstant. |
Note that all of the examples hold a conclusion parenthesis after their individual conditions are entered. The odd True/Artificial arguments are then left as part of the outside IF statement. You can likewise substitute Text operating theater Numeric values for the Admittedly/FALSE values to Be returned in the examples.
Here are some examples of using AND, OR and Non to evaluate dates.
Here are the formulas spelled out according to their logical system:
| Formula | Description |
|---|---|
| =IF(A2>B2,TRUE,Invalid) | IF A2 is greater than B2, return TRUE, other than come back FALSE. 03/12/14 is greater than 01/01/14, sol the formula returns TRUE. |
| =IF(AND(A3>B2,A3<C2),TRUE,FALSE) | IF A3 is greater than B2 AND A3 is less than C2, restitution Typical, otherwise return FALSE. In this case both arguments are sincere, so the formula returns Sincere. |
| =IF(Operating room(A4>B2,A4<B2+60),TRUE,Faux) | IF A4 is greater than B2 Operating theater A4 is to a lesser degree B2 + 60, return Echt, differently return FALSE. In this case the first argument is true, but the endorsement is false. Since OR only of necessity one of the arguments to be true, the formula returns TRUE. If you use the Appraise Formula Wizard from the Formula tab you'll see how Excel evaluates the formula. |
| =IF(NOT(A5>B2),TRUE,Artificial) | IF A5 is not greater than B2, and then return TRUE, otherwise return Treasonably. In this case, A5 is greater than B2, indeed the formula returns Delusive. |
Using AND, OR and Non with Conditional Formatting
You can as wel use AND, OR and NOT to set Conditional Format criteria with the formula option. When you do this you can omit the IF function and use AND, OR and NOT on their own.
From the Home base tabloid, click Conditional Formatting > New Normal. Next, select the "Use a formula to determine which cells to data formatting" pick, enter your normal and employ the formatting of your choice.
Using the earlier Dates example, present is what the formulas would equal.
| Formula | Verbal description |
|---|---|
| =A2>B2 | If A2 is greater than B2, initialize the cell, differently do nothing. |
| =AND(A3>B2,A3<C2) | If A3 is greater than B2 AND A3 is less than C2, arrange the cell, differently do nothing. |
| =Operating theatre(A4>B2,A4<B2+60) | If A4 is greater than B2 OR A4 is to a lesser degree B2 advantageous 60 (days), then format the cell, otherwise suffice nothing. |
| =NOT(A5>B2) | If A5 is NOT greater than B2, format the cell, otherwise do nothing. Therein case A5 is greater than B2, indeed the termination will return FALSE. If you were to change the formula to =Non(B2>A5) it would return TRUE and the cell would be formatted. |
Note:A common error is to enter your formula into Conditional Data format without the equals sign (=). If you coif this you'll attend that the Conditional Data formatting dialog will tot up the equals sign and quotes to the expression - ="OR(A4>B2,A4<B2+60)", so you'll need to remove the quotes in front the formula will answer properly.
Tipto of Varlet
Motivation more help?
You can always ask an expert in the Surpass Tech Community or obtain support in the Answers community.
See Also
Learn how to utilisation nested functions in a formula
IF function
AND function
OR function
NOT function
Overview of formulas in Excel
How to avoid broken formulas
Discover errors in formulas
Keyboard shortcuts in Stand out
Logical functions (reference)
Excel functions (alphabetical)
Excel functions (by family)
examples of functions that are not one to one
Source: https://support.microsoft.com/en-us/office/using-if-with-and-or-and-not-functions-d895f58c-b36c-419e-b1f2-5c193a236d97
0 Komentar