Write steps to assign Rank and Compute Bonus using VLOOKUP() function in Excel-2007
In the above excel table, Name, years of service and basic pay of employees are entered. Rank is given to an employee on the basis of years of service as follows:
And Bonus is percentage of basic as above. Write steps to assign Rank in column D and Compute Bonus in column E using VLOOKUP().
Year of Services | Upto 5 | Above 5 upto 15 | Above 15 upto 25 | Above 25 |
Rank | Silver | Gold | Platinum | Diamond |
Bonus | 10% | 15% | 25% | 40% |
To give rank in column D.
Enter the formula,
=VLOOKUP(B2,$G$4:$H$7,2)
in cell D2 and then press enter key, using the fill handle of cell D2, copy the formula to cells D3 :D20, by dragging the fill handle up to cell D20.
To compute Bonus in column E.
Enter the formula,
=VLOOKUP(D2,$H$4:$I$7,2)
in cell E2 and then press enter key, using the fill handle of cell E2, copy the formula to cells E3 :E20, by dragging the fill handle up to cell E20.
0 comments:
Post a Comment