#### 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%.

#### kchiba

##### Active Member
Can you give an example of how the numbers below the number you have changed will change

#### 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

#### trbharat

##### New Member
Hi..

Could you please mail me the file. My e-mail address is trbharat@gmail.com

Thank you..

#### 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