How to calculate revised gross pay with rating criteria?


Hi Excel Experts,

I have list of 10 employees who are yet to receive confirmation appraisal for this year, please find attached file.

In Gross Pay Column, salary of particular employee is mentioned.

Rating Criteria: If employee rated 1-3, he will receive 5% hike on his Gross Pay, 4, he will receive 10% hike on his Gross Pay, 5, he will receive 15% hike on his Gross Pay.

(The revised amount should reflect under Confirmation Column) 

Suggest a Suitable Formula:

For Rating and Confirmation Columns where if I enter rating between 1-5, excel automatically calculates percentage hike against the gross pay and gives me final value under confirmation column

Example: Mohit Sehgal receives 3, final value( 40,000+5%=42,000 should reflect under confirmation column). I want only be able to enter different ratings under rating column in which confirmation column gives value accordingly.

(Ignore PF Contribution column)  

Ratings for each employee available in sheet2.

Thanks and Regards,

Akash Sharma



Hi Variatus,
Thanks for the solution, but please dont mind I have seen that you try to make solutions too complicated not easy for excel freshers to understand.
My senior colleague suggested an easier and quick way using the IF Function under Confirmations Column.
In this way, no effort is to be used to create other worksheets and no need for vlookups. Whenever I enter any rating under Rating column, automatically value is generated under Confirmation Column.

Thanks so much for taking time and suggesting a good alternative. Appreciate it.
Akash Sharma 
Akash Sharma (rep: 24) May 26, '18 at 4:09 am
Hi Akash,
Thank you for posting the alternative solution. It would have been better had you offered it as an alternative answer to your own question. I think your argument is interesting for lots of visitors to this site. Nevertheless, I doubt that the formula you post is easier to understand than the one I suggested. You just feel that it is easier to manage and that is true, for now. Its one huge drawback is that you will have to review it when the rates change and, because the formula uses relative addressing, you will also have to review it if you make any changes to the columns in your worksheet. Chances are that you won't be able to do that without help and, anyway, it will take a lot of your time. My solution is easier to learn because it has fewer parts. But you are correct in observing that I advocate more preparation. To the novice that seems like unnecessary, extra effort. However, as your project develops your "easy" formula will give you trouble and start eating your time.
Variatus (rep: 2454) May 26, '18 at 9:29 pm
I would like to add a few words about preparations. I think you were planning correctly when you created a separate table for the ratings. That table has its own value and duties, perhaps beyond simply assigning a rating to each name. You will still need VLOOKUP to get the ratings from that table into your main sheet (included in my solution). When I suggested a separate table for the percentages I thought I was actually just continuing your own good idea. The rates table is also capable of having a life of its own. For example, it could be used to show the number of employees in each group or to calculate the total cost of increases and match it to a budget. It was with this in mind that I wrote that it didn't matter on which sheet you placed the table. Naturally, it should be on a tab of its own if you had more plans for it. It could be on any of the otehr sheets if or while you had not. However, the over-riding consideration is that you want to plan not to change formulas when data change. Keep data out of formulas, that is the cardinal rule. The percentages of increase are data and should therefore not be written into formulas.
Variatus (rep: 2454) May 26, '18 at 9:31 pm
Add to Discussion


Selected Answer

I have prepared a solution for you in the attached workbook. Please take a look. Observe that I included your Sheet3 in the solution.


Answer the Question

You must create an account to use the forum. Create an Account or Login