Summary
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.
Purpose
Test for a specific condition
Return value
The values you supply for TRUE or FALSE
Syntax
=IF (logical_test, [value_if_true], [value_if_false])
Arguments
logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
value_if_false - [optional] The value to return when logical_test evaluates to FALSE.
Usage notes
Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.
In the example shown, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in C2, copied down, is:
=IF(B2>=70,"Pass","Fail")
Translation: If the value in B2 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".
The logical flow this formula can be reversed. The formula below returns the same result:
=IF(B2<70,"Fail","Pass")
Translation: If the value in B2 is less than 70, return "Fail". Otherwise, return "Pass".
Both formulas above, when copied down, will return correct results.
Logical operators
When you are constructing a test with IF, you can use any of the following logical operators:
| Comparison operator | Meaning | Example |
| = | equal to | A1=D1 |
| > | greater than | A1>D1 |
| >= | greater than or equal to | A1>=D1 |
| < | less than | A1<d1< td=""></d1<> |
| <= | less than or equal to | A1<=D1 |
| <> | not equal to | A1<>D1 |
IF with AND, OR
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 use a formula like this:
=IF(AND(A1>7,A1<10),"OK","")
Translation: if A1 is greater than 7 and less than 10, return "OK". Otherwise, return nothing ("").
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 A1 is red or blue, return B1+10, otherwise return B1.
