Hello All. Let’s look at one of mostly used function – IF function. IF function performs logical test returns the specified value for TRUE or FALSE results.
Here, logical_test can use “=”, “>”, “<“, “>=”, “<=”, “<>” arguments.
Let’s look at examples for the IF function, then look at nested IF and IF with logical arguments.
In the below data set, lets look at simple check of marks to return PASS or FAIL. If marks are > 35, then return PASS, else return FAIL
If the logical test has a text to be verified, then enter the text with ” “. In the below example, we need to check if the color in column A is Brown. If value is not brown, then return “Other than Brown” else return “Brown”
Now, let’s look at nested IF (i.e.,) IF function within IF function. In the below data set, we need to check if the product is Pen and the color is Red. If both the conditions meet, then return “Premium” else return “Not Premium”
In the above example, first the function checks if the product is “Pen”, If true, then it executes the other IF function which checks if the color is Red. If Red, then it would return Premium, else not premium. Finally, if the product is not “Pen”, then it would return “Not Premium”.
Please note we can use upto 64 IF functions in the nested structure.
Let’s look at using logical AND / OR within IF function. In the same example as above, we are checking if product is Pen and if the color is Red. Instead of nesting multiple IF conditions, let’s use AND function to check both the arguments within a single function.
Please note, AND is used before the ( ) and the conditions are given within the ( ). The AND will check all the conditions and if every single condition is met, it would return “Premium” else “Not premium”
Now, let’s put all we saw above into one single formula. In the below data set, we need to find the grade for people who passed. If marks are > 85, then distinction, if >70 but <85, then first class, if >35 or <70, then second class.
Formula used: =IF(C4=”PASS”,IF(B4>85,”Distinction”,IF(AND(B4<=85,B4>70),”First Class”,IF(AND(B4<=70,B4>=35),”Second Class”,”Fail”))),”Fail”)
First, the function checks if the result is Pass. If not, return “Fail”. If yes, then use other IF conditions to check the marks. To check distinction, just >, to check others, we use AND function to check the ranges.
Please note that nesting IF may be powerful – however, one needs to be very careful while nesting too many conditions.
I hope you like this tutorial on IF condition. Please let us know your feedback which will help in getting our content better. Thanks for reading.