If This Is True Id Do It All Again Saosin
In Function one of our Excel IF part tutorial, we started to learn the basics and bolts of the Excel IF function. Equally y'all remember, we discussed a few IF formulas for numbers, dates and text values as well every bit how to write an IF statement for bare and non-blank cells.
However, for powerful data analysis, you lot may often need to evaluate multiple weather at a time, meaning you have to construct more sophisticated logical tests using multiple IF functions in one formula. The formula examples that follow below will show you lot how to do this correctly. Yous will as well acquire how to use Excel IF in array formulas and larn the basics of the IFEFFOR and IFNA functions.
How to utilize Excel IF part with multiple conditions
In summary, in that location can exist 2 basic types of multiple atmospheric condition - with AND and OR logic. Consequently, your IF office should embed an AND or OR function in the logical test, respectively.
- AND office. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the weather are met; otherwise it returns FALSE.
- OR function. In example y'all utilize the OR office in the logical test, Excel returns Truthful if any of the conditions is met; FALSE otherwise.
To better illustrate the point, permit'due south accept a look at a few IF examples with multiple conditions.
Example one. Using IF & AND part in Excel
Suppose, you have a table with the results of two test scores. The first score, stored in column C, must be equal to or greater than 20. The 2d score, listed in column D, must be equal to or exceed 30. Just when both of the above conditions are met, a student passes the concluding examination.
The easiest fashion to brand a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function:
Status: AND(B2>=20, C2>=30)
IF/AND formula:
=IF((AND(C2>=20, D2>=thirty)), "Pass", "Fail")
Piece of cake, isn't information technology? The formula tells Excel to render "Pass" if a value in column C >=20 AND a value in cavalcade D >=xxx. Otherwise, the formula returns "Fail". The screenshot below proves that our Excel IF /AND function is correct:
Notation. Microsoft Excel checks all conditions in the AND function, even if one of the already tested atmospheric condition evaluates to FALSE. Such beliefs is a bit unusual since in nearly of programming languages, subsequent conditions are not tested if any of the previous tests has returned False.
In exercise, a seemingly correct IF / AND formula may result in an fault because of this specificity. For case, the beneath formula will return "Split up by Goose egg Mistake" (#DIV/0!) if jail cell A2 is equal to 0:
=IF(AND(A2<>0,(1/A2)>0.5),"Good", "Bad")
The avoid this, you should employ a nested IF function:
=IF(A2<>0, IF((1/A2)>0.v, "Proficient", "Bad"), "Bad")
Example two. Using IF with OR function in Excel
You use the combination of IF & OR functions in a similar manner. The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met.
So, if we modify the above formula in the following way:
=IF((OR(C2>=20, D2>=thirty)), "Pass", "Neglect")
Cavalcade E will take 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.
Every bit you see in the screenshot below, our students have a amend chance to laissez passer the final examination with such conditions (Scott existence particularly unlucky failing by just 1 point : )
More than formula examples tin can be found in Excel IF OR function.
Example iii. Using IF with AND & OR functions
In case you take to evaluate your data based on several sets of multiple weather, you lot will have to employ both AND & OR functions at a fourth dimension.
In the in a higher place table, suppose you have the following criteria to evaluate the students' success:
- Condition ane: column C>=20 and column D>=25
- Condition two: cavalcade C>=15 and cavalcade D>=xx
If either of the above conditions is met, the final test is deemed passed, otherwise - failed.
The formula might seem tricky, but in a moment, y'all will see that it is not! Yous just have to limited two conditions as AND statements and enclose them in the OR function since you do not require both weather condition to be met, either volition suffice:
OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=twenty)
Finally, use the above OR function as the logical test in the IF function and supply value_if_true and value_if_false arguments. As the result, you will get the following IF formula with multiple AND / OR conditions:
=IF(OR(AND(C2>=xx, D2>=25), AND(C2>=xv, D2>=xx)), "Laissez passer", "Fail")
The screenshot beneath indicates that we've got the formula right:
Naturally, you lot are not limited to using only ii AND/OR functions in your Excel IF formulas. You can apply as many logical functions equally your business logic requires, provided that:
- In Excel 2016, 2013, 2010 and 2007, your formula includes no more than 255 arguments, and the full length of the formula does not exceed eight,192 characters.
- In Excel 2003 and lower, you tin can use up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.
Using multiple IF statements in Excel (nested IF functions)
If you lot need to create more than elaborate logical tests for your data, you can include additional IF statements in the value_if_true and value_if_false arguments of your Excel IF formulas. These multiple IF functions are chosen nested IF functions and they may prove peculiarly useful if you want your formula to render iii or more than dissimilar results.
Hither'southward a typical example: suppose you desire not simply to qualify the students' results as Pass/Neglect, but ascertain the total score every bit "Skilful", "Satisfactory" and "Poor". For example:
- Adept: 60 or more (>=60)
- Satisfactory: between 40 and lx (>twoscore and <60)
- Poor: 40 or less (<=40)
To begin with, y'all can add an additional column (E) with the following formula that sums numbers in columns C and D:
=C2+D2
And now, let's write a nested IF function based on the above conditions. It's considered a skilful practice to start with the virtually of import condition and brand your functions as simple every bit possible. Our Excel nested IF formula is as follows:
=IF(E2>=lx, "Good", IF(E2>xl, "Satisfactory", "Poor "))
As you lot run across, merely 1 nested IF function is sufficient in this instance. Naturally, you can nest more IF functions if you want to. For example:
=IF(E2>=70, "Fantabulous", IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor ")))
The above formula adds 1 more conditions - the total score of 70 points and more than is qualified as "Excellent".
For more information nigh Excel IF with multiple conditions, delight see How to use nested IF in Excel.
Using Excel IF in array formulas
Like other Excel functions, IF can be used in array formulas. You may need such a formula if you want to evaluate every element of the array when the IF statement is carried out.
For example, the following assortment SUM/IF formula demonstrates how you can sum cells in the specified range based on a certain condition rather than add upward the actual values:
=SUM(IF(B1:B5<=ane,1,2))
The formula assigns a certain number of "points" to each value in column B - if a value is equal to or less than 1, it equates to 1 point; and 2 points are assigned to each value greater than one. And then, the SUM part adds up the resulting 1's and 2's, as shown in the screenshot below.
Note. Since this is an array formula, call up to press Ctrl + Shift + Enter to enter it correctly.
Using IF function together with other Excel functions
Earlier in this tutorial, we've discussed a few IF formula examples demonstrating how to use the Excel IF function with logical functions AND and OR. At present, let's run across what other Excel functions tin can exist used with IF and what benefits this gives to you lot.
Example i. Using IF with SUM, Average, MIN and MAX functions
When discussing nested IF functions, we wrote the formula that returns different ranking (Excellent, Good, Satisfactory or Poor) based on the full score of each educatee. Every bit you remember, we added a new column with the formula that calculates the total of scores in columns C and D.
Just what if your table has a predefined structure that does not allow whatsoever modifications? In this example, instead of adding a helper cavalcade, y'all could add together values directly in your If formula, like this:
=IF((C2+D2)>=60, "Practiced", IF((C2+D2)=>forty, "Satisfactory", "Poor "))
Okay, but what if your table contains a lot of individual scores, say 5 different columns or more? Summing then many figures direct in the IF formula would get in enormously large. An alternative is embedding the SUM function in the IF's logical test, similar this:
=IF(SUM(C2:F2)>=120, "Expert", IF(SUM(C2:F2)>=90, "Satisfactory", "Poor "))
In a similar style, you lot tin use other Excel functions in the logical test of your IF formulas:
IF and AVERAGE:
=IF(AVERAGE(C2:F2)>=30,"Practiced",IF(AVERAGE(C2:F2)>=25,"Satisfactory","Poor "))
The formulas retunes "Good" if the boilerplate score in columns C:F is equal to or greater than 30, "Satisfactory" if the average score is betwixt 29 and 25 inclusive, and "Poor" if less than 25.
IF and MAX/MIN:
To find the highest and lowest scores, y'all tin utilize the MAX and MIN functions, respectively. Assuming that column F is the full score column, the below formulas work a treat:
MAX: =IF(F2=MAX($F$2:$F$10), "Best result", "")
MIN: =IF(F2=MIN($F$2:$F$10), "Worst issue", "")
If you'd rather have both the Min and Max results in the same column, yous can nest one of the to a higher place functions in the other, for case:
=IF(F2=MAX($F$2:$F$x) ,"Best event", IF(F2=MIN($F$2:$F$x), "Worst result", ""))
In a similar manner, you can apply the IF office with your custom worksheet functions. For instance, you can utilise it with the GetCellColor / GetCellFontColor functions to return different results based on a cell color.
In addition, Excel provides a number of special IF functions to analyze and calculate data based on different conditions.
For case, to count the occurrences of a text or numeric value based on a single or multiple conditions, you can use COUNTIF and COUNTIFS, respectively. To find out a sum of values based on the specified condition(south), use the SUMIF or SUMIFS functions. To summate the average according to certain criteria, use AVERAGEIF or AVERAGEIFS.
For the detailed pace-past-stride formula examples, cheque out the following tutorials:
- How to utilize the COUNTIF in Excel
- Using Excel COUNTIFS and COUNTIF with multiple conditions
- SUMIF in Excel - formula examples to conditionally sum cells
- How to use Excel SUMIFS and SUMIF with multiple criteria
Example 2. IF with ISNUMBER and ISTEXT functions
Y'all already know a way to spot blank and non-blank cells using the ISBLANK function. Microsoft Excel provides coordinating functions to identify text and numeric values - ISTEXT and ISNUMBER, respectively.
Here's is instance of the nested Excel IF role that returns "Text" if cell B1 contains any text value, "Number" if B1 contains a numeric value, and "Blank" if B1 is empty.
=IF(ISTEXT(B1), "Text", IF(ISNUMBER(B1), "Number", IF(ISBLANK(B1), "Blank", "")))
Annotation. Please pay attention that the above formula displays "Number" for numeric values and dates. This is because Microsoft Excel stores dates as numbers, starting from January 1, 1900, which equates to i.
Example three. Using the effect returned past IF in some other Excel role
Sometimes, you tin achieve the desired result by embedding the IF statement in some other Excel function, rather than using another function in a logical test.
Here'south another way how yous can apply the CONCATENATE and IF functions together:
=CONCATENATE("Y'all performed ", IF(C1>5,"fantastic!", "well"))
I believe you lot inappreciably demand any explanation of what the formula does, especially looking at the screenshot beneath:
IF function vs. IFERROR and IFNA
Both of the functions, IFERROR and IFNA, are used to trap errors in Excel formulas and replace them with some other calculation, predefined value or text bulletin. In earlier Excel versions, you tin utilize the IF ISERROR and IF ISNA combinations instead.
The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #North/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #Due north/A errors.
Hither is the simplest example of the IFERROR formula:
=IFERROR(B2/C2, "Sorry, an fault has occurred")
As you see in the screenshot above, column D displays the caliber of the division of a value in column B by a value in cavalcade C. Y'all can also meet two error messages in cells D2 and D5 because everyone knows that y'all cannot divide a number by zero.
In some cases, however, yous may not want to trap all errors, merely rather test the status causing a specific error. For example, to replace a divide by zero error with your own message, use the following IF formula:
=IF(C2=0, "Sorry, an fault has occurred", B2/C2)
And that's all I have to say nearly using the IF function in Excel. I cheers for reading and hope to see you on our weblog next week!
You may also be interested in
Source: https://www.ablebits.com/office-addins-blog/2014/12/03/excel-if-function-iferrror-ifna/
0 Response to "If This Is True Id Do It All Again Saosin"
Post a Comment