Updating a value to alter all other values used in a fix sum

[cplib]

Hi All

Apologies for the clunky title. I will try to explain more clearly.

I have a scenario where the total amount I can spend is 100. This is current distributed equally over 4 weeks 25,25,25,25.

I want to change one of the weeks to 40 and then have the rest of the weeks alter in proportion so that the total stays at 100 i.e. (100/40)/3 = 20

I can do this if I know which week I am changing and I have attached this as an example, however I cant see how I would create a formula that allowed me to change any week and have the other three weeks alter.

I have a few more advanced questions but hopefully someone can point me in the right direction to start me off!

Thanks

[Winon]

Hello cplib,

Maybe something along the lines of the attached sample Workbook?

Regards.

[Richard Buttrey]

Hi,

Since a cell can be either a manually entered value or a formula you'll need a Sheet Change macro. i.e.

Please Login or Register  to view this content.

[cplib]

Hi Winon

Thanks for your reply. This method doesn't let me alter B2,C2, or D2 and have the change reflect in the other three values.

Thanks

Craig

[cplib]

Hi Richard Buttery

I inserted this into a VB module and I can see that when altering one of the values in A:D if then updates the other three. However what doesn't happen is that it doesn't constrain the total to be 100. e.g. should I alter the value of A2 to 40 then B,C and D should be 20, if alter if to 55 then they should alter to 15.

Is there a way that this can be achieved?

Thanks for all your help.

[Richard Buttrey]

Hi,

First of all it shouldn't be in a VB Module. That's why I explicitly mentioned it needs to be a Sheet Change event. Also see the procedure name.

It will distribute the non manually entered cells so that the overall total of the 4 cells equals the column E cell. You do not need to enter a formula in column E, just the value that you want the cells to total to.

See attached.

[Winon]

Hello cplib,

Thank you for the feedback.

Richard Buttrey has given you a good start. I have tweaked his Code a little bit, (Sure he won't mind ).

Please see if the attached sample Workbook, is now closer to what you are looking for.