Most beginner Excel users know how to build simple formulas. Want cells A1 and B1 multiplied together? Just enter “=A1*B1” in a new cell. But what do you do when you want a certain result only when a specific condition is met? This is where the IF function can help.
For example, take a look at the spreadsheet below. In column A is a list of test scores from a recent 100 point exam. Let’s say you want to put the word “Pass” in column B, but only if the test score is 60 points or higher.
Excel’s IF Function
This can be accomplished by using the IF function in Excel. The IF function looks something like this:
IF(logical_test, [value_if_true], [value_if_false])
The first parameter, logical_test, is what you are testing for. In our example, we want to test if the exam score is greater than or equal to 60 points.
The second parameter, value_if_true, is what we want to display in the cell if our logical test is true.
The third parameter, value_if_false, is what we want to display in the cell if our logical test is false.
Pass or Fail?
Let’s see how we can apply this to our spreadsheet. In cell B2, we’ll enter the following formula:
A few things we need to point out here. Notice we used “>=” for our logical test. That’s because 60 is still a passing grade so we need to test for greater than or equal to 60.
For the second and third parameters, the words “Pass” and “Fail” need to be in quotation marks because they are literal string values. You could also replace these with cell references (which would not need to be in quotation marks) but we’ll keep it simple for now.
We’ll repeat this same formula in cells B3 through B11, being careful to update our column A references to A3 through A11 respectively. Alternatively, you could use the drag handle in the lower right corner of cell B2 to drag and copy the formula down to cell B11. Using this method, Excel will automatically update the references to the cells in column A for us.
You could also copy cell B2 and paste it in cells B3 through B11. Again, Excel will automatically update the references to the cells in column A for us.
No matter which method you choose, our spreadsheet should look like this once all of our formulas are in place:
Using a static cell reference for the passing score
We can make our spreadsheet easier to maintain if we take the literal value “60” out of our IF function and replace it with a static cell reference. We’ll start by creating a label and static value in cells D1 and D2 which you can see in our updated spreadsheet below.
Now let’s return to cell B2 and update our formula to the following:
Notice we changed the “60” in the logical test to “$D$2”. The dollar signs assure that our cell reference will stay at D2 when we copy and paste the formula in B2 to cells B3 through B11.
Once we update all of our formulas, the spreadsheet will not look any different. That wasn’t the point. The point was to make it easier to maintain.
For instance, if the school changes its policy to where a student must receive a 65 or higher to pass, rather than updating each formula in cells B2 through B11, we simply need to change the value in cell D2 from 60 to 65. Because our IF formulas are all referencing cell D2, the spreadsheet will instantly update all the Pass/Fail values in column B.
Note that the score of 62 in cell A6 changes the value in cell B6 from “Pass” to “Fail” when we update cell D2 to “65”.
Now that you know how to use Excel’s IF function, start thinking of where you could use it in your own spreadsheets.