In this 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.
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.|
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 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:
Remember; as the number of units in row 5 is not greater than 5 the formula would calculate a 5% commission.
Other Uses for IF
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
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 right-click this link and choose Save As (or equivalent for your browser) to download the spreadsheet used in the example above.
Want More Excel Formulas?
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.