How to use IF Function in Excel?

IF Function in Excel Definition with Formula:

The IF function in excel checks for one condition and accordingly returns a value that meets the first TRUE condition.

Formula: IF(logical_test, [value_if_true], [value_if_false])

IFS Function in Excel Definition with Formula:

The IFS function in excel checks if one or more conditions are observed or not and accordingly returns a value that meets the first TRUE condition.

Formula: IFS(logical_test1, value_if_true1, ………..) or we can say that

IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

Explanation with Example Questions:

Today’s lesson for Carl and Carly is the IFS and IF function in excel. We know from the previous day that Carl and Carly went to that market and bought some items and made a table of the price and discounts on those products.

To explain the concept, Brenda gave them these questions to solve-

1. To check if the item is on discount or not without using the discount column i.e. discount >0%. If yes, then print “discounted” else print “undiscounted”.
2. To check if the item is ‘Healthy’ or ‘on discount’ without checking the discount.
3. To check which snack cost them the most amount of money.

Carl and Carly both tried, and they were confused between the use of IF AND IFS in excel. Brenda helped them and explained the concept.

Coming to the question, here are the answers-

To check if the item is on discount or not and then print “discounted” or “undiscounted” accordingly.

Formula: IF(H4<F4,”Discounted”,”Undiscounted”)

• H4<F4– It is the logical condition.
• Discounted – This statement prints if the logical statement passes.
• Undiscounted – This statement prints if the logical statement fails.

To check if the item is ‘Healthy’ or ‘on discount’ without checking the discount.

Formula: IFS(E4:E10=”Fruits”,”Healthy”,H4:H10<F4:F10,”Discounted”)

• E4:E10=”Fruits”- Its logical_test1 and if the value matches “Fruits” then it will print “Healthy” else the condition will move forward and check other logical tests.
• H4:H10<F4:F10- Itslogical_test2 and if the values satisfy this condition then it will print “Discounted” else the condition will move forward and check other logical tests.

Check which snack cost the most amount of money.

Formula: IFS(H5>H4,”Banana”,H5>H6,”Banana”,H5>H7,”Banana”,H5>H8,”Banana”,H5>H9,”Banana”,H5>H10,”Banana”)

Answer: Banana will be printed

Here the first condition is satisfied i.e. H5>H4 so “Banana” is printed. But the issue with using IFS is that we can’t check the highest or lowest values as the answer is printed when the 1^st condition is met and other conditions will be ignored. This problem will be solved in the concept of AND function.

 Also, it prints ‘N/A’ when neither of the conditions passes the test. “That is also a disadvantage of using IF and IFS in excel“, says Brenda to her children. This problem will also be solved in use of AND function.

Carl and Carly were a little confused between the use of IF function in excel and IFS function in Excel, so Brenda explained in detail the differences.

• Single logical condition in IF function while Multiple logical conditions in IFS function.
• In the IF function, statement prints irrespective of the condition that passes or fails.
• In the IFS function, ‘N/A’ prints if all logical conditions fail.

These differences cleared all the doubts of Carl and Carly about the use of IF and IFS in excel.