Urgent Help with Improving a Balancing Macro

Dear Experts

I urgently need your help with my problem about a macro that I have written to balance the "Existing Totals" in my spreadsheet with "Expected Totals". The Expected Totals are given values (i.e. pre-specified) at an aggregated level. I need to use them to adjust my detailed data so that their totals become the same as the new totals (i.e. Expected Totals).

I have worked out a Macro, and a method, for balancing (please see attached). The Macro does not perform well for all cases. Perhaps it requires some adjustment, addition of extra codes etc, to get it going well for all cases.

I have spent half a day to set up the file attached to make it easier for you to provide me with your response. In this file, I have described the situation and the problem. Also, I have commented out cell values and methods to make it easier for you to go through.

It has been a challenging task for me. I have exhausted all of my tricks to get it going, but it appears that on my own I can't progress it further.

I need your urgent help as I am in middle of my work and it does not allow me to progress.

I have include a sample of data that the Macro can handle it well; and a sample of data that it stops after going through many iterative loops of reducing balances to zeros.

I look forward to receiving your assistance as soon as possible (please see attached file).

Best Regards
Simon

Hi
Many thanks for assisting me.

The method to reduce the balances to zero, is exactly as you have noted.
The Table 1 gets populated with the original data after it was FIRST linked to the Table 0 (which contains our original unbalanced data).
Then the method is to copy contents of Table 3 to Table 1 and repeat this through loop until balances in the right hand side of each Table
(i.e. Tables 1 to 3) becomes all ZEROs.

Please note the type of formulas in each cell in all these tables (Table 1, Table 2, Table 3 as well as in Table 0, Table 5 and Table 6.

Also please see the spreadsheet tab called "description of the problem".

The method of balancing is controlled by the formulas in Tables 1, Table 2 and Table 3.

You can write a better formulas, macro or program and method for balancing totals.

The method that I have used through these Tables are only things that I could develop for this purpose.
I think, it is the standard approach but I am not sure. Maybe there is a better way of doing it too.


Please let me know if I can assist with further clarification.

Hope to receive your hepful solution soon.

Simon

Dear Experts

Could anyone assist me with this query please?

I am sure there should be a better way to solve the problem. The Macro I have written works fine for some cases but it stops working for other cases. Please have a look and assist if possible at all.

Best Regards
Simon

What about mathematically solving it?

Correct row * Correct column / Grand Total

Using Table 1 as reference, in cell C29

Formula:

Please Login or Register  to view this content.

Another side note, after inspecting your calculations and deciphering your table...

It seems that the data that the macro cannot balance is because of inconsistent row and column totals.

In 'a) Data that Macro CAN Balance', the totals matches SUM(H6:H10,H13) = H18 - H4 = 25190
In 'b)Data that MacroCANNOT Balance', the totals don't match SUM(H6:H10,H13) = 30228 <> H18 - H4 = 28565

Hi quekbc

Thank you for your feedback. Much appreciated.

I tried this mathematical solution in the past. The issue is that it changes the numbers brutally providing no control on how much numbers can be changed, or what is the accuracy tolerance for change.
For example in one instance some of the positive numbers (among other large and small positive numbers and one negative number among them) were
changed to negative numbers for the sake of balancing the totals (while some large numbers were also adjusted as I expected). Those negative numbers were not meaningful in my analysis.

Then, I tried the Excel Macro with the hope to put some control around the change, or directing change to be focused more around certain large positives i.e. large numbers and
less on smaller numbers. I couldn't achieve this.

I acknowledge your side-notes re exact totals for row and column totals as recently I picked that up in the real data I am using. Nevertheless, I came across cases where totals were correct but Macro didn't work.

Therefore, I thought I post what I have done in case experts like yourself could suggest a method to achieves my balancing aim while it is done consciously with some rules
around it so that numbers are not drastically changed while the totals are balanced.

I appreciate solutions to help me with my aim.

Many thanks for your contributions.

Best Regards
Simon

HI Simon,

Regarding negative numbers, mathematically speaking it should not happen as all it relies upon are row totals, the column totals, and the grand totals. If row totals are positive, column totals are positive, the grand total must be positive, and as such, the formula I suggested above should never result in a negative.

Without looking too much into the details, the macro you introduced seems to achieve the same result as the formula I proposed, just done differently. Maybe give this another try and see how it goes. If anything, it should resolve most of your tables without the need of a macro - and those special cases can be dealt with manually.

Cheers,
quekbc

Hi
Thanks for your feedback. Regarding your assertion that when all numbers, totals and grand totals are positive then the resultant balanced values are also positive, I have attached two real examples of data that I am using for your information. They are all positive values based on your comment. However, the mathematical solution has produced some negative values that are not suitable for my analysis.

The only option to manually adjust, as I have tried, is to turn them back to what they were (i.e. not balanced figures). If you have a solution for manual adjustment please share with me.

What we need to keep in mind, as I have added an image of the real data, is that the numbers in the question are part of the columns with certain totals. those totals can not be changed.
Therefore, manual adjustment should be in a way that the actual totals of the data in question are maintained so that the other totals that are dependent on them are not changed.

Please share with me your thought after examing the attached examples.

Once again many thanks for your comments. I am hoping to come up with a practical solution.
It is a very challenging question to solve considering its connection with so many other numbers too.

Best Regards
Simon