Let’s take a look. To illustrate how IF works, let’s look first at a case where we need to assign a “pass” or “fail” to a group of students. We have five test scores in columns D through H, and an average in column I. To pass, students need to achieve an average of 70 or greater. Anything lower is a fail. This is a perfect application for the IF function. To start off, I’ll type an equal sign, IF, and an opening parentheses. The IF function takes three arguments: a logical test, a value if “true,” and a value if “false.” Notice that both [value_if_true] and [value_if_false] are optional; however, IF requires at least one of these arguments, so we’ll use [value_if_true] for now. So, to pass, a student needs an average of 70 or greater. That means we can write our logical test as I6 >= 70, and simply enter “Pass” in double-quotes for the value if true. If I copy that value down, we’ll see which students passed. What if we want to do it the other way around, and show which students failed? In that case, we just need to adjust the test to be I6 < 70, and set the value if true to “Fail.” Now the formula reports which students failed. Notice that because we didn’t supply value if false, the formula returns FALSE for students with an average over 70. Let’s adjust the formula to show both pass and fail. To do that, I need to add the third argument for value if false. Since we’re testing to see which students have scores below 70, the value if true is “Fail,” which means the value if false should be “Pass.” Now each student in the list gets either a pass or a fail. Let’s do one more thing to make this example more interesting. Let’s make the passing score an input on the worksheet that can be easily changed. I’ll also name the input cell to make the new formula easier to read. Now I just need to update the formula to use the input cell and copy it down. With the passing score exposed as an input, we can change the passing score to any value we like and all results update dynamically.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
