The IF function is very versatile. You can use it whenever you need to test a condition and take an action based on the result. You can even combine multiple IF statements to check multiple conditions.
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 < 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 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.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....