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.|
Excel IF Function Explained
In this Microsoft Office Online Training tutorial we’re going to explain how to use the IF function (also known as IF Statement), and look at a couple of different applications for it.
With the IF statement 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 function as:
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 statement 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:
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 Function
We don’t have to use the IF function 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 IF formula would look like this:
=IF(D2>5,"Pay 10%","Pay 5%")
Notice the difference between the two formulas are 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 function
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.
Want More Excel Formulas
Why not visit our list of Excel formulas. You’ll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy
Plus 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 free.
Did you find this useful, or did it just confuse you? Let us have your comments below.