Excel 2007 – IF Statement Explained

Watch the video extract as seen on YouTube, then read the full tutorial below.

Click the Full Screen button on the player to watch it in HD.

To get the full video with bonus tips for using IF Statements sign up for our free Microsoft Office Online Training.

Excel IF Statements Explained

In this Microsoft Office Online Training tutorial we’re going to explain how to use the IF Formula, and look at a couple of different applications for it.

With the IF formula you can tell Excel to perform different calculations depending on whether the answer to your question is true of false.

The function wizard in Excel describes the IF Formula as:

= IF(logical_test,value_if_true,value_if_false)

But let’s translate it into English and apply it to an example:

In the table below we want to calculate a commission in column G for each Builder based on the number of units in column D.

We’ll say that for units over 5 we’ll pay 10% commission based on the Total $k figure in column F, and for units of 5 and under we’ll pay 5% commission.

Our IF formula for row 2 would read like this:

=IF(The number of units in cell D2 is >5,Then take the Total $k in cell F2 x 10%, but if it’s not > 5 then take the Total $k in cell F2 x 5%)

The actual formula we would enter into Cell G2 would be:

=IF(D2>5,F2*10%,F2*5%)

Remember; as the number of units in row 5 is not greater than 5 the formula would calculate a 5% commission.

Other applications of the Excel IF statement

We don’t have to use the IF statement to perform a calculation.  We could use it to return a comment.   If we take the previous example again, we could have asked Excel to put a note in the cell like ‘Pay 5%’ or ‘Pay 10%’.  To do this our formula would look like this:

=IF(D2>5,”Pay 10%”,”Pay 5%”)

Notice the difference between the two formulas is the inverted commas (“) surrounding the results we want Excel to produce.  These inverted commas tell Excel that the information between them is to be entered as text.

Below is a screen shot of how the formula looks in the Formula Bar and the result returned in column G.

Try other operators in your IF statements

Because the IF formula is based on logic, you can employ tests other than the greater than (>) operator used in the example above.

Other operators you could use are:

• =             Equal to
• <             Less Than
• <=           Less than or equal to
• >=           Greater than or equal to (if we’d used this operator in our above example row 5 which had 5 units would have returned Pay 10%)
• <>           Less than or greater than

Now practice.  Copy and paste the formulas used in these examples into your workbooks to practice, or download the spreadsheet used in the example above.

Click here to get more free Microsoft Office online training and video tutorials from My Online Training Hub. 

If you like the IF statement, take a look at our tutorials on SUMIF and SUMIFS Formulas Explained and COUNTIF and COUNTIFS Formulas Explained.

Did you find this useful, or did it just confuse you?  Let us have your comments below.

Want more on Excel? Sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you’ll get our 100 Excel Tips & Tricks e-book.

Tagged as: Excel IF Statement, Excel Online Training, Excel Tips and tricks, Excel Training, Excel Tutorials, Free Excel Training, Free Online Training, Free Online video tutorials, Microsoft Excel Training