Calculate Percent Increase Based On Matrix

    Hi,


    I need to calculate the % of salary increase and bonus for staff based on a matrix.


    1) The salary increase in based on the salary grid. Each staff is allocated a ratio as shown in cell H4 to H25. For example, for Abraham, he has obtained a rating of 2 and has a ratio of 78%. Looking at the salary grid, he should get a 13% salary increase.


    2) The bonus is also based on a bonus grid. The bonus is based on the rating and job time.The date used for current date calculation is Jan 11 2008. For example, Abraham has a rating of 2 and a job time more than 18 months.Therefore, he should get a bonus of 24.99% based on the bonus grid.


    I am trying to create a formula in salary increase% column (yellow) and the bonus % column (blue) to automatically update from the salary grid and bonus grid. The actual staff number is quite long and using a correct formula would ensure the data ia accurate based on the grid/matrix.


    Appreciate any help.

    Re: Calculate Percent Increase Based On Matrix


    Hi,


    I am not sure how to use vlookup on this one. The vloopup reads based on one criteria.The scenario that I have has multiple criteria.


    For example,


    1) On column I, for Abraham, since he has a 2 rating and a 78% ratio, he should get a 13% salary increase based on the salary grid which shows that a 2 rating with a ratio of 75% to 79% is 13% increase in salary.


    2) On column J, for Abraham, since he has a job time of 43 months, the bonus grid indicates that with a 2 rating and a job time of more than 18 motnhs, he should get 24.99% of bonus.


    Is is possible to use vlookup for both the criteria above or any other formula? I tried IF and AND but it I am not getting it right.


    Appreciate assistance.

    Re: Calculate Percent Increase Based On Matrix


    Hi,


    For the bonus grid, if the staff has 3 months or less, then we would not pay any bonus.


    I tried expanding the Lookup formula and adjusting the grid but it still states "N/A".


    Example is cell K10, the staff has a job time of 3 months. Therefore, based on the bonus grid, he will not receive any bonus.


    Appreciate assistance.

    Re: Calculate Percent Increase Based On Matrix


    If the format for the bonus grid is consistent (E62:E77 in ascending order with respect to their corresponding rating), try...


    K4, copied down:


    =INDEX($B$62:$B$77,MATCH(H4,IF($D$62:$D$77=E4,IF($E$62:$E$77<=H4,$E$62:$E$77))))


    Otherwise, try...


    =INDEX($B$62:$B$77,MATCH(MAX(IF($D$62:$D$77=E4,IF($E$62:$E$77<=H4,$E$62:$E$77))),IF($D$62:$D$77=E4,$E$62:$E$77),0))


    Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.


    Hope this helps!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login