Forum Discussion
Calculating percentage increases using a scoring system and a set budget amount
I need to be able to calculate a wage increase on individuals. For each person, I have their score from 7-15 (note that multiple people can have the same score), their current annual rate, and the total budget by department. The number of employees in each department will vary, so I can’t use any kind of set number for any kind of formula. It has to be able to determine how many are in the department.
I need to be able to calculate what percentage of increase each person should get so that the higher ranked employees get a higher percentage and the ones below that get spread out equitably by their score, but where the sum of their increases uses up the budget amount, but doesn’t go over.
I can’t seem to wrap my head around what this formula needs to be.
Sample of data:
| EE Name | Score | Current salary | Total budget amount | 350,000 |
| Employee 1 | 7 | 52,998.40 | ||
| Employee 2 | 7 | 55,993.60 | ||
| Employee 3 | 7 | 52,000.00 | ||
| Employee 4 | 7 | 55,993.60 | ||
| Employee 5 | 7 | 52,998.40 | ||
| Employee 6 | 7 | 56,000.05 | ||
| Employee 7 | 7 | 48,048.00 | ||
| Employee 8 | 7 | 54,995.20 | ||
| Employee 9 | 7 | 43,680.00 | ||
| Employee 10 | 7 | 43,680.00 | ||
| Employee 11 | 7 | 44,990.40 | ||
| Employee 12 | 7 | 57,990.40 | ||
| Employee 13 | 7 | 43,680.00 | ||
| Employee 14 | 7 | 51,001.60 | ||
| Employee 15 | 7 | 55,993.60 | ||
| Employee 16 | 7 | 55,993.60 |
maybe something like:
= [range of scores]/sum( [range of scores] ) * [Budget Amount]
so if the scores are in B2:B20 and the budget is E1 then
= B2:B20 / sum(B2:B20) * E1
5 Replies
maybe something like:
= [range of scores]/sum( [range of scores] ) * [Budget Amount]
so if the scores are in B2:B20 and the budget is E1 then
= B2:B20 / sum(B2:B20) * E1
This worked! Thank you.
See Increase.xlsx
Change the total budget amount to see how the percentages change.
Thank you!
I bumped the total budget up to 350k, but the results in column G only increased to the 51,252.80 that was initially in column E. Do I need to change something else in conjunction with that?
Apparently I interpreted your request incorrectly - try the suggestion by m_tarler
