In this example we want to take the employees individual ratings and categorize them into one of 5 bands.
- In D11 to D30, create a formula that will lookup the rating in column C to match the appropriate description from the table in K11 to L15 so for example a rating of 3.6 should bring back an ‘Inconsistent’ description.
You need to group your employees into various age categories for your reporting.
- In E14 column, create a lookup the compares the age in column D, to the table in A4 to B8 and brings back the relevant description, e.g. an age of 36 would return ‘Between 35 and 50’
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
Why is the VLOOKUP’s in J8 to L8 pulling through information relating to SP010 instead of the correct SP0101?
To see the full course contents click here.