# Bands in excel exercise 2

This is a preview lesson

In this example you are calculating the tax liability based on various salaries. The tax table is provided (D20 to F26) and a series of salaries are provided (A30 to A34). You need to calculate what the tax liability will be on these amounts.

• In B30 use VLOOKUP to determine what the fixed portion will be based on the salary in A30 and the tables shown in the A30 table
• In C30 use VLOOKUP to determine what the % for that salary level
• In D30 use VLOOKUP to determine what the lower limit is of the band (HINT: You can use 1 as your column number)
• In E30 calculate the amount to apply the % to e.g. a salary of R150 000 would be R150 000 less the band it falls into (R140 000) so R10 000.
• In F30 calculate the % portion of the tax (based on E30 and C30)
• In G30 pull through the fixed portion from B30
• In H30 add the % portion and fixed portion together to get a total tax bill
• Copy down to row 34
• In H35 calculate the total tax bill and compare it to H36. If they are not the same find out what the problem is

## Video Instructions

To follow along go to 5_4_1

## Video Solution

To follow along go to 5_4_2

0 of 23 lessons complete (0%)
0
0