Recalculate percentages

[SDowse]

Hi,

Not entirely confident this is possible, but here is my situation.

I have 6 cells with percentage values in, I have hard typed these values in. What I want to do is to be able to adjust one percentage value and the other 5 percentage values automatically update but keeping the proportional value...if that makes sense?

Any ideas if this is possible?

 

[AlanY]

a shot in the dark, with your originals in A1:F1, input a new% within B2:F2 with new calculated % in A3:F3 (copy A3 across to F3)

Excel 2013/2016
	A	B	C	D	E	F
1	10%	20%	30%	40%	50%	60%
2				25%
3	6%	13%	19%	25%	31%	38%
Sheet1

Cell Formulas
Range	Formula
A3	=A1*INDEX($A$2:$F$2,MATCH(1000000,$A$2:$F$2))/INDEX($A$1:$F$1,MATCH(1000000,$A$2:$F$2))

 

[SDowse]

Hi - thanks for the post, but I can't get this to work. I have entered a new % and the recalculation happens, but the total % is greater than 100%...

 

[dreid1011]

Based on the layout AlanY suggested above, perhaps this:

=IF(LOOKUP(1.01,$A$2:$F$2,$A$1:$F$1)>SUM($A$2:$F$2),IF(A2="",A1+((LOOKUP(1.01,$A$2:$F$2,$A$1:$F$1)-SUM($A$2:$F$2))/5),A2),IF(A2="",A1-((SUM($A$2:$F$2)-LOOKUP(1.01,$A$2:$F$2,$A$1:$F$1))/5),A2))

This formula assumes there will only be one % changing at a time. If more than one % can change, then someone else may have to help.

However, it seems to be slightly inaccurate if the % change is not evenly divisible by 5. Then again, this only applies if you are not showing any decimal places. It works okay with 2 decimal places showing.

 

[SDowse]

Hi - many thanks. That seems to work and I can certainly have it so only 1 % changes at a time. Thanks for all the help!

 

[dreid1011]

You're welcome.