Quick, clean, and to the point

The IF function

The IF function is a very flexible function. You can use it whenever you need to test a condition and take an action based on the result.

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.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.