Using the Excel IF Function

As you approach a stop light, it might be green, yellow, or red. You decide what to do based on the color. If it is green, you go. If it is yellow, you proceed with caution. If it is red, you stop. Using the IF function, you can have your spreadsheet make similar decisions. The IF function, evaluates a condition. If it is TRUE, it returns one value. If it is FALSE, it returns another value.

The syntax:

=IF(condition, value to return if TRUE, value to return if FALSE)

Example 1: You want to check cell B1 to determine if it is equal to 100. If it is, you want Excel to return 1000. if it is not, you want Excel to return 800. Enter the following formula:

=IF(B1=100,1000,800)

Example 2: You want to check cell B2 to determine if it is greater than or equal to 70. If it is, you want Excel to return "Pass." If it is not, you want Excel to return "Fail."

=IF(B2>=70,"Pass","Fail")

Example 3: The third argument – value to return if FALSE – is optional. If you do not include it, if the condition is not TRUE, Excel returns FALSE.

=IF(B3=100,1000)

Excel returns 1000 if B3 is equal to 100 and FALSE, if it is not.

NESTED IFs

Example 4: If you have more than one condition, you have to nest your IF functions ( unless you are using Excel 16 or higher). For example, you want to check cell B4; and, if it is equal to 1, you want Excel to return 100. If it is equal to 2, you want Excel to return 200.  If it is anything else, you want Excel to return "Try Again."

=IF(B4=1,100,IF(B4=2,200,"Try Again"))

You can look at the formula this way. If B4 is equal to 1, the condition is met so return 100. If B4 is not equal to 1, check to see if it is equal to 2; if it is, return 200; if it is not, return "Try Again."

Note: If you are using Excel 16 or higher, you may be able to use IFS.

Example 5: You can have multiple levels of nests. For example, you can modify Example 4. This time check cell B5 and add the condition: if B5 is equal to 3, return 300.

=IF(B5=1,100,IF(B5=2,200,IF(B5=3,300,"Try Again")))

The AND Function

A logical is an expression that evaluates to either TRUE or FALSE. The AND function checks one or more logicals. If they are all true, it returns TRUE; otherwise, it returns FALSE.

The syntax:

=AND(logical 1, logical 2, …)

Examples:

The AND function is useful when you are working with the IF function and you want to include a range of values.

Example 6: You want to find out if the value in cell B6 is less than 100 and greater than 74. Enter the following formula:

=AND(B6<100,B6>74)

If B6 is less than 100, the first logical will return TRUE. If B6 is greater than 74, the second logical will return TRUE. If both logicals are TRUE, the function will return TRUE. In this example, only values between 100 and 74 will return TRUE. In other words, 100 will not return TRUE and 74 will not return TRUE.

Example 7: Now, you have enough information to solve the following problem. You have a person who is going to receive a score between 100 and 0. Their score is stored in cell B7. If they receive a score of 100, they receive $1000; if they receive a score between 100 and 74, they receive $800.00; if they receive a score between 75 and 49, they receive $600;  if they receive a score of less than 50,  they receive $400. Enter the following formula to determine the amount they receive:

=IF(B7=100,1000,IF(AND(B7<100,B7>74)=TRUE,800,IF(AND(B7<75,B7>49)=TRUE,600,IF(AND(B7<50, B7>-1),

400,"Invalid Entry"))))

All of the examples are illustrated in the worksheet that follows. Feel free to test the formulas and modify them any way you like. The Demo tab contains a practical example of Example 7.