Of all the many functions in Excel, the IF function is often the first function new users turn to. It’s a very flexible function that you can use in all sorts of ways.
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 a five test scores in columns D through H, and 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 3 arguments: a logical test, a value if true, and a value if false. Notice that value if false is optional, so let’s skip that argument for the moment.
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 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 adjust the test to be I6
Notice that because we didn’t supply value if false, the formula returns FALSE for students with an average above 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 worksheet so that it 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 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.