• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Solver

trbharat

New Member
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.
 

trbharat

New Member
There was an error in the last post..


please read the last line as "....... but could NOT find the solution"
 

Hui

Excel Ninja
Staff member
Do you want the remaining pieces to maintain their relativity to each other or change evenly ?
 

trbharat

New Member
I want top maintain their relativity. i should be able to set constraints for each of the below values..


for example (relating to the example used to describe my problem):

53% can change to any value from 49% to 56%. 21% can change to any value in the range 20% to 25%.
 

trbharat

New Member
ok... continuing with the same example...


The numbers initially were 5, 6, 53, 21 and 14.


I have kept the first number same and changed the second number from 6 to 11.

And i have given the conditions for below cells in this way: 53 can change to any value from 49 to 56. 21 can change to any value in the range 20 to 25 and the last number 14 cannot be changed...


Now the sequence i got is 5, 11, 49, 20 and 14. This is just an example.


If instead of 6, i had changed 5 to 8. Here i shall add a condition that 6 can change from 6 to 12. then the sequence i got is 8, 6, 50, 21 and 14. In all the cases, the sum should be same (=99).


I hope I have managed to explain what i wanted.
 

kchiba

Active Member
Hi Bharat,


I have worked out the formulas for the changes, but have ignored the ranges that you set.


The calculation requires a number of helper columns and rows.


I have done this in Excel 2007, please let me know where I can upload the file for you.


cheers


kanti
 

kchiba

Active Member
Hi Bharat,


Glad to know it worked, have sent you a second file with a slightly more elegant solution


kanti
 

nagovind

Member
Could you guys post the excel file in this fourm it is highly appreciated

use rapidshare.com to post the files please
 

kchiba

Active Member
Hi Nagovind,


I am not able to access any of the share sites from my computer, they are all blocked.


kanti
 
Top