In this 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)
Now let’s explain in English and apply it to an example:
This function tests a condition, If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE. Depending upon the result, one of two actions will be carried out.
Below In the sample table we want to calculate a commission rate in column G for each Sales Rep based on the number of units in column D.
We’ll say that for all 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 a screen shot of how the formula looks in the Formula Bar and the result returned in column G.
Try these 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
Practice Time: Copy and paste the formulas used in these examples into your workbooks to practice
Download Example spreadsheet used in this tutorial
Follow on with the SUMIF and SUMIFS Formulas Explained and COUNTIF and COUNTIFS Formulas Explained.
