Announcement

Collapse
No announcement yet.

Calculate Percent Increase Based On Matrix

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Attached Files

  • #2
    Re: Calculate Percent Increase Based On Matrix

    Create a Lookup table and use VLOOKUP like;

    =VLOOKUP(H4,SalaryIncrease,2)

    Comment


    • #3
      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.

      Comment


      • #4
        Re: Calculate Percent Increase Based On Matrix

        Hi,

        See the attachment.

        HTH
        Attached Files
        Kris

        ExcelFox

        Comment


        • #5
          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.
          Attached Files

          Comment


          • #6
            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!

            Comment


            • #7
              Re: Calculate Percent Increase Based On Matrix

              That worked !

              Thank you Krishnakumar and Doeminic.

              Really aprreciate your help.

              Comment

              Working...
              X