The Excel IF function runs a logical test and returns 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.
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.
The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The first argument, logical_test, is an expression that should return either TRUE or FALSE. The second argument, value_if_true, is the value to return when logical_test returns TRUE. The last argument, value_if_false, is the value to return when logical_test returns 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.
Pass or Fail example
In the worksheet shown above, we want to assign either “Pass” or “Fail” based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:
=if(C5>=70,"Pass","Fail")
Translation: If the value in C5 is greater than or equal to 70, return “Pass”. Otherwise, return “Fail”.
Note that the logical flow of this formula can be reversed. This formula returns the same result:
=if(C5<70,"Fail","Pass")
Translation: If the value in C5 is less than 70, return “Fail”. Otherwise, return “Pass”.
Both formulas above, when copied down, will return correct results.
Assign points based on color
In the worksheet below, we want to assign points based on the color in column B. If the color is “red”, the result should be 100. If the color is “blue”, the result should be 125. This requires that we use a formula based on two IF functions, one nested inside the other. The formula in C5, copied down, is:
=if(B5="red",100,if(B5="blue",125))
Translation: IF the value in B5 is “red”, return 100. Else, if the value in B5 is “blue”, return 125.
There are three things to notice in this example:
- The formula will return FALSE if the value in B5 is anything except “red” or “blue”
- The text values “red” and “blue” must be enclosed in double quotes (“”)
- The IF function is not case-sensitive and will match “red” or “Red” or “RED”