Help with a percentage changes based on prior percent

[schavarria]

Hello all!

I've been requested to give a user the ability to change a percentage of workload among employees and have the other employees' percentages adjust automatically based on how they were prior to the adjustment.

For instance:

Employee A: 10%
Employee B: 20%
Employee C: 30%
Employee D: 40%

The user wants to change Employee A to now do 15% of the work so the other amounts need to be adjusted to balance the decrease in their work.

Employee A: 15%
Employee B: 16.7%
Employee C: 26.7%
Employee D: 36.6%

Any help on this would be greatly appreciated!! Thank you in advance.

 

[Max1616]

Hello, In the future, providing a spreadsheet for us to work out of would be nice. Also I noticed that your new % do not add up to 100.

What I have done is created a spreadsheet that will make these updates for you. but what I am doing is changing the other employee's work load proportionately to the amount of workload they currently have.

For instance, if employee A is now taking an extra 5%, then there needs to be 5% removed from the other employees. So if employee B currently has 22.22% of the remaining original workload (20/90), then I will take 22.22% multiplied by 5 and adjust their workload by that amount.

I cant seem to post attachments here, so I posted it to Ozgrid.
For schavarria

Sincerely,
Max

 

[Max1616]

Oh, I also forgot to mention that you can add new employees and the userform and formulas will account for the new employees. The only thing to keep in mind is Column C needs to be empty