Macro Recalculating List in order to have the always the same sum

[kaligad]

Hello,

I have the following problem. In excel I have two columns - A and B. Each column has 10 rows with different values. I would like to be able to write a "0" on a random value and then being able to recalculate the values in the column, adding the missing sum to each cell.
AS IS situation:

A |B
10 |20
10 |30
20 |40

When I put 0 in stead of "10" in cell A1, I would like column A to be recalculated as follows:

A |B
0 |20
15 |30
25 |40

Any Ideas?

[MickG]

Try this:-

Please Login or Register  to view this content.

Regrds Mick

[kaligad]

Good Work, MickG!

Now it becomes a little more complicated. The way it works, it only takes into account if one ZERO is put. What should I do, if I want to put "0" in both A1 and A2?
The idea is that the rest should be evenly distributed. In the bast case scenario I should be able to put 9 times zero and at the end I will have the whole SUM of the column in the tenth cell, where no 0 is present.

I hope you understand what I mean.

[MickG]

Try this:-

Please Login or Register  to view this content.

Regrds Mick

[kaligad]

Works like a charm!
Indeed!

Another question? (do not answer me, if you do not want..., you have already helped me a lot )
What would the model look like if we implement another rule? Let's say we have the following:

40
20
20

And we decide to delete the 20. With the model now, we would receive:
50
0
30

What I want, is to receive the following:
54
0
26

The reason to add 14 to 40 and 6 to 20, is due to the fact, that in the initial values, 40 was twice bigger than 20, so it should get twice more.
I was able to make a solution like this in Excel, but it uses two additional columns for the calculations.

Thank you once again! It is really good to see someone who actually understands what is in your head
Have a nice evening

[MickG]

I understand the basic logic , althought 6 & 14 are not strickly proportionate to 40 & 20, so you would need a rule that could be applied to the whole range of numbers, because although you have only ever shown 3 row in your example, you initially spoke about 10 rows and thats what the code was based on.

[kaligad]

Yes, exactly. If it would make it easier, consider that you have the sum as value (not as formula) on row 11.

And to make it even crazier, can you think of a solution with an array?

[MickG]

I think you will need to show a Before and After example and explain the logic.

[kaligad]

Yes, before and after is a good idea Thus you can always return to the first figures, just by copying them.

But can you really think of an array solution for this? It would be really crazy.