The formula might seem tricky, but in a moment, you will see that it is not! You just have to express two conditions as AND statements and enclose them in the OR function since you do not require both conditions to be met, either will suffice: If either of the above conditions is met, the final exam is deemed passed, otherwise - failed.
Condition 2: column C>=15 and column D>=20. Condition 1: column C>=20 and column D>=25. In the above table, suppose you have the following criteria to evaluate the students' success: In case you have to evaluate your data based on several sets of multiple conditions, you will have to employ both AND & OR functions at a time. More formula examples can be found in Excel IF OR functon. =IF((OR(C2>=20, D2>=30)), "Pass", "Fail")Ĭolumn E will have the "Pass" mark if either the first score is equal to or greater than 20 OR the second score is equal to or greater than 30.Īs you see in the screenshot below, our students have a better chance to pass the final exam with such conditions (Scott being particularly unlucky failing by just 1 point : ) So, if we modify the above formula in the following way: The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met. You use the combination of IF & OR functions in a similar way. The avoid this, you should use a nested IF function: For example, the below formula will return "Divide by Zero Error" (#DIV/0!) if cell A2 is equal to 0: In practice, a seemingly correct IF / AND formula may result in an error because of this specificity. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE. Microsoft Excel checks all conditions in the AND function, even if one of the already tested conditions evaluates to FALSE. The screenshot below proves that our Excel IF /AND function is correct: =IF((AND(C2>=20, D2>=30)), "Pass", "Fail")Įasy, isn't it? The formula tells Excel to return "Pass" if a value in column C >=20 AND a value in column D >=30. The easiest way to make a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function: Only when both of the above conditions are met, a student passes the final exam. The second score, listed in column D, must be equal to or exceed 30. The first score, stored in column C, must be equal to or greater than 20. Suppose, you have a table with the results of two exam scores. To better illustrate the point, let's have a look at a few IF examples with multiple conditions. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met FALSE otherwise. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met otherwise it returns FALSE. Consequently, your IF function should embed an AND or OR function in the logical test, respectively. In summary, there can be 2 basic types of multiple conditions - with AND and OR logic. How to use Excel IF function with multiple conditions Using IF together with other Excel functions. Multiple IF statements in Excel (nested IF's). Excel IF function with multiple AND/OR conditions. You will also learn how to use Excel IF in array formulas and learn the basics of the IFEFFOR and IFNA functions. The formula examples that follow below will show you how to do this correctly. However, for powerful data analysis, you may often need to evaluate multiple conditions at a time, meaning you have to construct more sophisticated logical tests using multiple IF functions in one formula. As you remember, we discussed a few IF formulas for numbers, dates and text values as well as how to write an IF statement for blank and non-blank cells. In Part 1 of our Excel IF function tutorial, we started to learn the nuts and bolts of the Excel IF function.
0 kommentar(er)
