IF function and nested IF function has been a lifeline of many excel users for a long time now. And quite frankly it has the ability to solve many problems by building complex logic structures.
However in 2016, Microsoft introduced a new function called IFS in excel which is a better option than the nested If formula. We will first begin with IF function & Nested IF functions to help beginners understand its usability, then we will move on to the IFS function so that we can see the benefits it has over nested IF function. Let's go!
Dataset Used
This data set consists of the marks secured by the students in various subjects.
People who are well versed with IF & Nested IF functions, can skip to the part where we explore the IFS function.
Let us first understand IF function
Most basic explanation for IF function is when you have to instruct excel to do a certain thing when a condition is met or else do another thing if condition is not met.
Let's see this with an example using the student dataset.
Problem Statement
Create a new column called “Math Grade” and in that column write a formula which will give us the result as “Grade A” if the Math score is greater than or equal to 70, else if its less than 70 then “Grade B”
So we can enter the formula below to solve this problem:
=IF(F2>=70,"Grade A", "Grade B")
Formula Explanation : First part of the formula is the logic test, where we are saying check if the value in F7 is greater or equal to 70. If it is true then it gives us “Grade A”. In the else condition we don't have to write any logic test because there are only 2 conditions that we need to check, so if one is false then automatically it will give us “Grade B”
Let us now look at nested IF function
Nested IF are used when we have more than 2 conditions to check. In that case normal IF function will not suffice the requirement. So we write an IF function within another IF function, which is called a Nested IF function.
Problem Statement
Create a new column called “Scholarship Eligibility” and in that column write a formula which will give us following result
If Test Preparation Course = “None” then “Not Eligible”
Else if Math + Reading + Writing score >= 210 then “Eligible”
Else if Math score >= 95 then “Eligible”
Else “Not Eligible”
So to solve this problem we can enter below formula and drag it till the end
=IF(E2 = "none","Not Eligible",IF(F2+G2+H2 >= 210, "Eligible",IF(F2>= 95, "Eligible", "Not Eligible")))
As you see it's a long formula but we have covered all 4 conditions using multiple IF functions within other IF functions. Every nested IF is written when the condition is false or not met in the previous IF function
Now lets see how using IFS function is a breeze as compared to Nested IF
We are going to use the same problem statement but this time we will use IFS function instead
=IFS(E2="None", "Not Eligible",F2+G2+H2 >= 210, "Eligible",F2>=95, "Eligible",TRUE,"Not Eligible")
Formula Explanation
In the IFS function we don't need to worry about opening multiple brackets or writing multiple IF statements. IFS just has 2 parameters i.e. Logic Test & Value if True. And for writing the final else statement, we just have to use TRUE, which is an inbuilt excel function which treats the last condition as true if all previous ones are false.
So the construction of the IFS formula is much simpler & easy to manage!
댓글