# Thread: Max Increase (Excel 2003)

1. ## Max Increase (Excel 2003)

Hi Excel Experts,

I am looking for a formula to fix the max increase i need to give to an employee. I am attaching the file for your reference.

The file has three sheets
Benchmark-07 - Has the median Salary details for all levels
Matrix - Has the Increment Matrix
Master Data - The the employee data.

Col F - Is the current Salary, Col G - Pre increase Compa (Current salary/Benchmark for the level), Col H is the Proposed Increment % based on Col E & G. Col I - New Salary, Col J - New Compa (New Salary/Benchmark for the level)

I need a formula in Col H over and above the existing formula. If Col G is > 115% then 0% Increase. However based on the matrix if the new increase is less than 120% Compa, the differential should be put in Cashout i.e. Col K.
Also, if Col G is < 115% and the recommended increase is >115%, then 115% and the differentials should be put in Col K, the amount not exceeding 120% i.e. 115% + Cashout not > 120%

For Eg if Col G is 105% and the Recommended Increase is 15% (Col H), the Actual Output should Give an Increase upto 115% i.e. 10% and the differential as cashout not > 120%

Hope my question is clear, else let me know if you have any clarification.

Looking forward to a great and simple formula :-).

Regards
Baiju

2. ## Re: Max Increase (Excel 2003)

What do you mean by "However based on the matrix if the new increase is less than 120% Compa, the differential should be put in Cashout i.e. Col K."? What differential?

3. ## Re: Max Increase (Excel 2003)

Hi Rory,

If the employee is >115% and is T20 under Technology the employee will get 6.3% increase, which will take him to 121.3%, however would like to restrict the Increase to 0%, the differential between 115 to 120 to be put in Cashout.

Hope this clarifies.

Regards
Baiju

4. ## Re: Max Increase (Excel 2003)

Hi Rory,

Hope this example helps.

For those who are currently above 115% compa ratio (prior to any increase), a Cashout (not more than) 5% is to be applied (if they’re at 117% then a 3% Cashout will apply)
For those whose compa after increases is above 115% increase to max 115% balance Cashout not greater than 120%.
For example: Mr. X is currently on 112% compa, he is a 2A and the increase recommended is 7%- apply just a 3% TEC increase and the balance as a cash out (but not beyond 5% or 120%)
For those above 120% at any stage will not receive an increase or payout

Regards
Baiju

5. ## Re: Max Increase (Excel 2003)

Let's look at line 15 specifically. Pre increase compa is 101.4%. Implied increase is 13.9% but that would give a new Compa of 115.5%. Should the increase be used at 13.9% because 13.9% is less than 15%, or should the increase be capped at a level that makes the final compa 115% exactly, with the balance going in to the cashout column?

6. ## Re: Max Increase (Excel 2003)

Does this do what you want?

7. ## Re: Max Increase (Excel 2003)

Hi Rory,

At the out look like you have got what i exactly was looking at... thanx a ton... i have close to 1500 employee.. i shall use this formula and see if it works fine.

You have made my job very easy.

Thanks a ton.

Regards
Baiju

8. ## Re: Max Increase (Excel 2003)

Hi Rory,

Thanks for the formula, however a small tweeking needs to be done.. if current compa > 115% & Proposed Increase is Zero as per the Matrix, then Zero NO Cashout
AND if Current compa >115% and Proposed Increase is > Zero, then Cashout upto 120% Compa

Also in some cases, the new increase + Cashout is greater than the proposed increase without the upper limit of 115% compa. and in some cases Increment + Cashout is greater than120% compa.

I have attached a file with the earlier formula without the Max 115% compa and your proposed formula.

Regards
Baiju

#### Posting Permissions

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