How to use the Excel IF function

Excel If Function

The Excel IF Statement tests a given condition and returns one value for a TRUE result and another value for a FALSE result. For example, to "Pass" percentage above 60: =IF(C6>=60, "Pass", "Fail"). More than one condition can be tested by nesting IF function. The IF function can be combined with logical functions like AND and OR to extend the logical test.

Syntax

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

Return value

The values you supply for TRUE or FALSE

Perpose

Test for a specific condition.

Arguments

1. logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
2. [value_if_true] - The value that is returned if the logical_test is TRUE.
3. [value_if_false] - The value that is returned if the logical_test is FALSE.

Usage notes

The IF function is used to run a logical test, and reacts differently depending on whether the result is TRUE or FALSE. The first argument, logical_test, is an expression that returns either TRUE or FALSE.

Both [value_if_true] and [value_if_false] are optional. but at least one of them must be provided. The result from IF can be a value, a cell reference, or even another formula.

In the example shown above, we want to assign either "Pass" or "Fail" based on a test score. A passing percentage is 60 or higher. The formula in D6, copied down, is:

=IF(C6>=60,"Pass","Fail")

Translation: if the value in C6 is greater than or equal to 60, return "Pass". Otherwise, return "Fail" .

The logical flow of this formula can be reversed. The formula below returns the same result:

=IF(C6<60,"Fail","Pass")

Translation: if the value in C6 is less than 60, return "Fail". Otherwise, return "Pass" .

Both formulas above, when copied down, will return correct results.

Another formula

The IF function can return another as a result. For example, the formula above will return D5*500 when C5 is equal to "AC", and D5*200 when C5 contain another room type like "GENERAL":

=IF(C5="AC",D5*500,D5*200)

Nested IF statements

The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible result. Each IF statement needs to be carefully "nested" inside another so that the logic is correct.

For example, the following formula can be used to assign a grade rather than a pass / fail result:

=IF(C5<60,"F",IF(C5<70,"D",IF(C5<80,"C",IF(C5<90,"B","A"))))

** Up to 64 IF function** can be nested. However, in general, you should consider other function like VLOOKUP or HLOOKUP to handle more complex logic. because they can handle more condition.

Logical Operators

When you are constructing a test with IF, you can use any of the following logical operators:

IF with AND, OR, NOT

The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use a formula like this:

=IF(AND(A1>7,A1<10),"OK","")

Translation: if the value in A1 is between 7 and 10, return "OK". Otherwise, return an empty string.

To return B1+10 when A1 is "red" or "blue" you can use the OR function like this:

=IF(OR(A1="red",A1="blue"),B1+10,B1)

Translation: if the value in A1 is "red" or "blue", return the value in B1 plus 10. Otherwise, return the value in B1.

=IF(NOT(A1="red"),B1+10,B1)

Translation: if the value in A1 is not "red", return the value in B1 plus 10. Otherwise, return the value in B1.

GET EXCEL FILE