I shall explain my problem using the below example.
I have a column of 5 numbers - 345, 435,3567, 1423 & 975. The next column shows the contribution of the each number to the total. (Sum of 5 numbers is 6745, contribution of each number to the total is 5%, 6%, 53%, 21% and 14% respectively).
What i want is this:
The Total (Sum of all numbers) is fixed. Now, if i change any percentage figure, all the values below it should change such that the sum is always 100%. From the above example, if I change 6% to 11%, the values below 6% i.e 53%, 21% and 14% should change in such a way that total percentage is 100%. I should be able to set conditions as to what level those figures can change. Also, all the values above the one should not change.
Is it possible to do this with Excel. I have tried using Solver, but could NOT find the solution.
I have a column of 5 numbers - 345, 435,3567, 1423 & 975. The next column shows the contribution of the each number to the total. (Sum of 5 numbers is 6745, contribution of each number to the total is 5%, 6%, 53%, 21% and 14% respectively).
What i want is this:
The Total (Sum of all numbers) is fixed. Now, if i change any percentage figure, all the values below it should change such that the sum is always 100%. From the above example, if I change 6% to 11%, the values below 6% i.e 53%, 21% and 14% should change in such a way that total percentage is 100%. I should be able to set conditions as to what level those figures can change. Also, all the values above the one should not change.
Is it possible to do this with Excel. I have tried using Solver, but could NOT find the solution.