How to Use an If Then Statement in Excel
The IF function, or statement, says to a computer program, "If this happens, then do this." Many programming languages use IF statements to determine what direction a program should go. In Microsoft Excel, you can use an IF statement but with a different syntax. Instead of explicitly entering "IF (this) THEN (that)," you provide a logical test value, then program what the spreadsheet should do if the test evaluates to "true" and what it should do for "false." Like all functions in Excel, start the formula using the equals sign followed by calling the function.
Other People Are Reading
Instructions
-
-
1
Open a spreadsheet. As a sample statement, click on cell A1 and type:
=IF(B1>50, "Value over 50.", "Value not over 50.")
This tells Excel that when the value in cell B1 is greater than 50, A1 shows "Value over 50." When it is below or equal to 50, A1 shows "Value not over 50."
-
2
Click on cell A2 and type another sample IF statement:
=IF(B2>C2, "B2 is greater than C2", "B2 is not greater than C2.")
This IF statement compares values in two cells -- B2 and C2, in this case -- instead of testing the value of one cell.
-
-
3
Click on cell A3 and type:
=IF(B3>SUM(C3:F3),"B3 is greater than C3:F3.","B3 is not greater than C3:F3.")
This IF statement shows that you can use other Excel formulas as part of the logical test. In this example, it checks to see whether the value in cell B3 is or isn't greater than the sum of the values in cells C3 to F3.
-
4
Click on cell A4 and type:
=IF(B4>50,IF(B4>75,"Value greater than 75.","Value between 50 and 75."),"Value not greater than 50.")
This statement uses a nested IF statement that allows you to test for more than two outcomes. In this example, the IF statement tests for a value less than 50, greater than 50 and less than 75, and a value greater than 75. You can nest up to 64 IF statements in one cell, but this is probably way more than you will ever need. Note that the nested IF statements do not require an equals sign to call them.
-
1
Tips & Warnings
Although Excel allows up to 64 nested IF statements, you might find it easier to use Excel's LOOKUP function if you have more than a few possible outcomes.
