How a Nested IF Function Works in Excel?
The IF function in Excel offers decision making capabilities to Excel worksheets. An IF function in Excel evaluates a logical test which has two possible outcomes: true or false. The function will carry out an action if the result is true and performs another action if the result is false. In short, the IF function helps you carry out different actions depending on whether the condition is true or not. But, in most of the real world scenarios, there could be more than two possible outcomes. That’s where nested IF functions come into play. Nested IF function works by replacing one or both of the true/false calculations with another If function. You are allowed to nest up to sixty four functions in Excel 2007 and 2010. By increasing the nested IFs, you are increasing the number of possible outcomes that can be tested for and also the number of actions that can be implemented.
The syntax of the nested IF function is:
IF (condition1, value-if-true1, IF (condition2, value-if-true2, value-if-false2))
Here, condition is the logical condition to be tested, value-if-true is the value returned if the condition evaluates to true and value-if-false is the value returned if the condition evaluates to false. You have the freedom to specify the actions to be carried out by making value-if-true and value-if-false specific. The action could be execution of a formula, clearing a cell text or even inserting a text statement.
Suppose, you want to decide the grade of students based on their mark. Suppose, the grade calculation is to be done as follows:
|>=80 and <90||A|
|>=60 and <80||B|
|>=40 and <60||C|
Now, let’s use the data given below to illustrate the concept of nested IF function. The grade calculation details are also given in the same spreadsheet so that we can refer those values from the spreadsheet. You will be able to find the minimum marks required attaining a specific grade from the Excel worksheet itself, thus you are will be more clear. This method has an added benefit. Even if you change the conditions, you will not have to change the formula. For example, after implementing the procedure, the school decides to change the grades as A*, A+, B* and so on. Then, you just have to make changes in the spreadsheet, so that the reference in the formula will be automatically updated.
You can either directly enter the formula in cell D2 or use the IF function dialog box to enter the arguments. After making the nested IF function ready in cell D2, we can copy the same formula to cells D3 to D11. Let’s proceed with the second method. However, we cannot open a second dialog box to enter the nested conditions. First, click on cell D2 and then go to Formulas -> Logical ->IF. Now, you will get the function arguments window. In the Logical_test textbox, enter the condition C2>=90 because the score is in cell C2. In the Value_if_true textbox, enter the value $H$5. We are using absolute referencing here because when we copy the formula from cells D3 to D11, the cell that referencing grade should not change. In the Value_if_false textbox, enter the formula, IF(C2>=80, $H$6, IF(C2>=60,$H$7,IF(C2>=40,$H$8,$H$9))). Now, your Function Arguments window will look like this:
Click OK and you will get the grade B in the cell D2. Now copy the formula from cells D3 to D11. You will get the grades of all ten students in the respective cell D as given below.
Read More about Excel here