Redistribute Values Formula Advice

[ExcelRaceRatings]

Hello,

I am looking for advice with a mathematics formula. Say in Cells A1:A4 i have the below 4 percentage values:

A1 - 45%
A2 - 25%
A3 - 20%
A4 - 10%
Total - 100%

I am looking to set up a formula in cells B1:B4 that if i change one of those % values in A1:A4 that the changed value be redistributed among the the remaining 3 values?

Say in A1:A4 i change A3s value from 20% to 10% then i want that 10% to redistributed among the values in A1, A2 & A4.

Hope that makes sense and thank you in advance!

 

[Peter_SSs]

You won't be able to have formulas in A1:A4 to change their own values. If you want the new values still in A1:A4 you will have to use vba code.

However, some further guidance on what you want would be required. In the example below, if we change A2 from 9% to 0% do we ..
a) Add an extra 3% to each of the others as shown in B1:B4, or
b) Do we add the majority of that spare 9% to A1, like I have done in column C, since it had by far the largest value already, or
c) Do something else?

Excel 2016 (Windows) 32 bit
	A	B	C
1	80%	83%	87%
2	9%	0%	0%
3	6%	9%	7%
4	5%	8%	6%
5	100%	100%	100%
Sample

 

[ExcelRaceRatings]

Hi Peter,

Thanks for your response.

Evenly redistributing the values among the remaining as you have shown in Column B would my the path i would like to go down.

 

[Peter_SSs]

Evenly redistributing the values among the remaining as you have shown in Column B would my the path i would like to go down.

Hmm, that could be problematic.
Take my column A example again and suppose we change the 5% to 50%. That is, we added 45% to that cell. With your request, that would mean subtracting 15% from each of the other three cells. That would make cells A2 and A3 negative. Is that what you want?