Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use the IF function

Tags 
Summary 
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.
Video Transcript 

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.

Author 
Dave Bruns
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables