In this tutorial we’re going to explain how to use the Excel 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.
Excel IF Function Explanation
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:
|<=||Less than or equal to|
|>=||Greater than or equal to*|
|<>||Less than or greater than|
*If we’d used this operator in our above example row 5 which had 5 units would have returned Pay 10%.
Download the Workbook
Now it's your turn to practice. Download the Excel file used in the example above.
Enter your email address below to download the sample workbook.