Excel Formula Help – nested IF statements for calculating employee income tax
In Microsoft Excel, the IF function is one of the most common and most useful. An IF statement simply sates that if a condition is true, then do this.., if false, then do that...
The basic syntax of an IF statement describes a comparison between values and follows =IF(logical _ test, [value if true], [value if false]) Let's see how we can use IF in an Excel formula.
Below, we can see two tables. The first table refers to income tax bands and their associated % calculation and thresholds. The second offers a number of employee salaries; as is usually the case, the more you earn, the more you are taxed.
Let's begin with Jenny. First, we need a formula that compares her total salary with the relevant tax band maximum, and then calculates her income based on the relevant tax percentage. In C9, the formula written out will be =IF(salary is less than $29,100, then multiply by the income tax % of tax band 1) (Here we are using numeric values in E5 and E6 so that B4 to B6 can be formatted as text)
This will give us Jenny's income tax level, but if we try to copy down the formula to Ralf and Kurt, it will not work, for two reasons:
First, the formula does not take into account what should happen if a salary is greater than tax band 1. To do this we need to add a nested IF statement that tells Excel what to do if a salary is equal to or greater than the tax band 2 threshold, and the same for tax band 3.
This we do by adapting our formula so that, again written out, it says =IF(salary is less than $29,100, then multiply by the income tax % of tax band 1, however! IF (this is not the case and..(salary is less than $72,000 - the limit of tax band 2 - then multiply by band 2 income tax %, or if salary is greater than $72000, use tax band 3 for % calculation))
The formula so far would then look like this = IF ( B9 < E5, B9 * C4, IF ( B9 < E6, B9 * C5, B9 * C6 ) ) This is not, of course, the way real income tax is calculated but it servers to demonstrate the uses of nested IF formulas.
This still will not provide a complete fix. Before we are ready to copy down to Ralf and Kurt, we need to fix all cell references in the formula that must remain unchanged, in other words, any cell reference that is not B9. This we do with Absolute References. Simply select the cell references in the formula an use F4 to add in $ symbols. The final formula, as seen in the diagram below, should read: = IF ( B9 < $E$5, B9 * $C$4, IF ( B9 < $E$6, B9 * $C$5, B9 * $C$6 ) )
We are then ready to copy down:
For more help with creating Excel calculations to fit your organisations needs, contact our experts.
