Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Calculate Percent Increase Based On Matrix

  1. #1
    Join Date
    28th May 2006
    Posts
    133

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Calculate Percent Increase Based On Matrix

    Create a Lookup table and use VLOOKUP like;

    =VLOOKUP(H4,SalaryIncrease,2)

  3. #3
    Join Date
    28th May 2006
    Posts
    133

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,304

    Re: Calculate Percent Increase Based On Matrix

    Hi,

    See the attachment.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    28th May 2006
    Posts
    133

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,275

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th May 2006
    Posts
    133

    Re: Calculate Percent Increase Based On Matrix

    That worked !

    Thank you Krishnakumar and Doeminic.

    Really aprreciate your help.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Calculate Increase Based On Criteria
    By eli_kpg in forum EXCEL HELP
    Replies: 5
    Last Post: January 19th, 2009, 11:20
  2. Increase/Mark-up Cost By x Percent
    By hblack in forum EXCEL HELP
    Replies: 3
    Last Post: April 15th, 2008, 13:10
  3. 6.70 X 10% Est. Percent Increase Or Decrease
    By Fargo95 in forum EXCEL HELP
    Replies: 1
    Last Post: April 25th, 2007, 17:16
  4. Formula to show percent increase/decrease
    By AmyWin in forum EXCEL HELP
    Replies: 3
    Last Post: August 4th, 2005, 00:51
  5. Replies: 1
    Last Post: May 26th, 2004, 20:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno