Forum Discussion
Help with a formula to redistribute percentage change between other values
Dear Excel community,
I was wondering if someone could help me with another Excel conundrum ?
In the attached work book, i have material composition and the respective tonnages.
I want to allow people to adjust the percentage values in column B but whatever percentage change they apply, the variance between the old value and new is rebalanced amongst the other material types.
So for example if MRF Glass (cell B22) is reduced from 35.40% to 30.00%. I would like Excel to redistribute the 5.40% points equally between the other material types. This should work both ways in being able to increase or decrease a value. The total percentage should not exceed 100%
I hope the above makes sense ?
I'm not even sure if this is possible but any help would be welcome as usual.
Kind regards
Raj
So I did what I think you want but noticed a problem and propose a variation.
So first off you will have issues manually changing the value and having a formula changing the existing values due to circular references and overwriting formulas so 1st suggestion is to define the original as the 'Default' values. Then have a column for the 'Requested' values and then the output. See the below image (and attached file):
So in this case I requested the Alu cans to be 5% so the 3.46% was equally reduced across all the other items but items that were 0% went to -0.2%. So then I did the change proportionatly so MRF glass at 35.4% changed by -1.24% but HDPE coloured at 0.84% changed by only 0.03%.
Hope that helps
4 Replies
So I did what I think you want but noticed a problem and propose a variation.
So first off you will have issues manually changing the value and having a formula changing the existing values due to circular references and overwriting formulas so 1st suggestion is to define the original as the 'Default' values. Then have a column for the 'Requested' values and then the output. See the below image (and attached file):
So in this case I requested the Alu cans to be 5% so the 3.46% was equally reduced across all the other items but items that were 0% went to -0.2%. So then I did the change proportionatly so MRF glass at 35.4% changed by -1.24% but HDPE coloured at 0.84% changed by only 0.03%.
Hope that helps
Try on below:
- Create a New Column for Adjustments: In Column C, enter the adjusted percentage values. For example, if the original percentage for MRF Glass is in B22, and you want to adjust it to 30%, enter 30% in C22.
- Calculate the Total Adjusted Percentage: In a cell, let's say D1, calculate the total adjusted percentage excluding the cell you changed. Use a formula like:
=SUM(B2:B22) - B22 + C22
This formula subtracts the original value in B22 and adds the adjusted value in C22.
- Calculate the Redistribution Value: In another cell, D2, calculate the redistribution value:
=B22 - C22
- Redistribute the Change: In Column D (or another new column), redistribute the value calculated in D2 equally among the other materials. For each cell in D2 to D21 (assuming 22 materials total), use the formula:
=B2 + ($D$2/(COUNT(B2:B22)-1))
