I have a predetermined percentage distribution for a set of 5 elements, for example:
A = 25%
B = 15%
C = 30%
D = 20%
E = 10%
Then, I get all these elements in some other distribution ratios, for example:
A = 20%
B = 20%
C = 20%
D = 15%
E = 25%
And I also know their sum in some units, let’s say their sum is 100 for easier calculation purposes.
Is there a way to predict how you need to change these to reach the standard distribution considering you can only add things and not subtract?
When there are only two such elements, it’s doable:
So, for the first element:
=IF(ELEMENT1_GIVEN_PERCENTAGE < ELEMENT1_STANDARD_PERCENTAGE; (ELEMENT_2_NUMBER – (ALL_ELEMENT_SUM * ELEMENT2_STANDARD_PERCENTAGE)) / (ALL_ELEMENT_SUM * ELEMENT2_STANDARD_PERCENTAGE;0)
For example, if standard A = 32% and B = 68%, and we received A = 40 and B = 31, which is A = 56% and B = 44%, the normal equation to calculate how many B’s we need to add to get to the standard percentages will be 40/(71+x)=0.32, x = 54.
And for the second, you just flip it.
So based on which element is above the required percentage, you basically calculate how much of another element you need to add to reach the required distribution.
But once you add more elements, I don’t see how one can do it. Any advice? Is there some standard function I am missing? Thanks!
Also, I thought about it like a non-linear equation system, for example, if I had three elements, it would look like:
(7 + x)/(y + x + z + v) = 0.25
(1 + z)/(y + x + z + v) = 0.45
(2 + v)/(y + x + z + v) = 0.3
where y is the initial sum of all elements.