Excel IF Function (10 Examples)

Excel IF Function

The Excel IF function returns a value for TRUE and another result for FALSE. For example, if we write the formula =IF(B2>75, “Pass”, “Fail”) will return “Pass” if the value of B2>75. The Excel IF function allows to test multiple conditions by nesting IF. IF function can also be combined with AND/OR.

In this section:

---

1. Syntax of IF Function.
2. IF statement to calculate grades
3. IF Function when a single condition is met
4. IF function with condition based on text
5. Calculate discount if sales>$15,000
6. To check whether something is DONE
7. IF with wildcard (?, **)
8. IF with EXACT Match
9. IF/AND
10. IF/OR
11. IF/NOT

---

1. Syntax:

=IF (logical_test, [value_if_true], [value_if_false])

where:

1. logical_test:  The user given condition that would be tested and evaluated as either TRUE or FALSE.
2. value_if_true: The returned value if the logical test is TRUE. This is an optional argument.
3. value_if_false: The returned value if the logical test is FALSE. This is an optional argument.

2. IF statement to calculate grade:

To calculate letter grade, the IF formula is =IF(D5>=90, “A”, IF(D5>=85, “A-“, IF(D5>=80, “B+”, IF(D5>=75, “B”, IF(D5>=70, “B-“, IF(D5>=65, “C”, “F”)))))), which returns the respective grades.

3. IF Function when a single condition is met:

To return a value when one condition is met, the formula is =IF(C5>80, “High”, “Low”), which returns “High” if temperature is more than 80 and “Low” if temperature is less than 80.

4. Condition based on text:

When users set a condition on text, the text must be enclosed double quotation (” “). The formula is =IF(C5=”Excellent”, 1,2), which returns 1 if performance is “Excellent” and 2 if not.

5. Calculate discount if sales>$15,000:

We want to calculate discount if sales>$15,000. The formula is =IF(C5>=15000, C5*0.1, “No Discount”), which returns the amount of discount if sales>$15,000.

6. Check whether something is done:

To comment on whether something is done, the formula is =IF(C5=”done”, “Yes”, “No”), which returns “Yes” if done, and “No” if not done.

7. IF Function with wildcards( ?, **):

To use IF function, we need to combine the ISNUMBER and SEARCH with IF (In case of case insensitive), and ISNUMBER and FIND with IF (In case of case sensitive). The formula is =IF(ISNUMBER(SEARCH(“don”, C5)), “Yes”, “Need Works”), which returns values if matched partially.

8. IF Function with EXACT Match:

To find values with exact match, the formula is =IF(EXACT(C5, “DONE”), “Yes”, “No”), which returns “Yes” if it finds exact match.

9. IF with AND criteria:

We want to admit students based on score in math and statistics. If scores both in math and statistics>80, then “Yes”; otherwise, “No”. The formula is =IF(AND(C5>80, D5>80), “Yes”, “No”), which returns “Yes” if both conditions are met.

10. If with OR criteria:

We want to admit students if they score more than 80 either in Math or in Statistics. The formula is =IF(OR(C5>80, D5>80), “Yes”, “No”), which returns “Yes” if score in Math OR Statistics>80.

11. IF with NOT:

If value is NOT equal to certain value, then return “yes”. The formula is =IF(NOT(C5>80), “No”, “Yes”).

More readings:

MS Office post on IF

Excel CHOOSE Function

Excel AVERAGE Function