auto change 'sum' values but keep total constant

[Zio]

Hello Guys, i stuck with one table, hope you can help me.
Here is a short example:
A
1| 5
2| 10
3| 15
4| 30
5| 60
row 5 is =SUM(A1:A4).

So when i change the value of A1 from 5 to 10 the total will change to 65.
I want when i change A1 from 5 to 10 to keep the total 60, and the other values in A2,A3,A4 to be changed according to total, maybe on a percent basis depends from changed value. And maybe i will need additional column because i will not change only A1 value, could be A3 or A4 for instance.

 

[mohanyathish]

You will have to insert two columns...

A6= New Total

B1=(A1*100)/$A$5
B2=(A2*100)/$A$5
B3=(A3*100)/$A$5
B4=(A4*100)/$A$5

C1=(B1*$A$6)/100
C2=(B2*$A$6)/100
C3=(B3*$A$6)/100
C4=(B4*$A$6)/100

C1 to C4 will give the new values for the new total....

I hope this helps....

 

[Zio]

Thanks for the reply mohanyathish, but i cant catch...

C1 to C4 will give the new values for the new total ...which is A6?

Looking again my example i think this will be very complicated formula...if exist at all

 

[mohanyathish]

Provide your new total at A6...

the values from C1 to C4 will change to accommodate the new total value...

instead of entering in A5..u will be entering the value in A6...