Forum Discussion
Help with a formula to redistribute percentage change between other values
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))
