Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculate Percent Increase Based On Matrix

1. Senior Member
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.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Calculate Percent Increase Based On Matrix

Create a Lookup table and use VLOOKUP like;

=VLOOKUP(H4,SalaryIncrease,2)

3. Senior Member
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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Calculate Percent Increase Based On Matrix

Hi,

See the attachment.

HTH

5. Senior Member
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.

Excel Video Tutorials / Excel Dashboards Reports

6. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

## 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. Senior Member
Join Date
28th May 2006
Posts
133

## Re: Calculate Percent Increase Based On Matrix

That worked !

Thank you Krishnakumar and Doeminic.

Excel Video Tutorials / Excel Dashboards Reports

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

#### 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