Balance Rows and Columns

Hi Experts, I am looking for some help on the best way to solve an issue I have presented with.

I have a list of items (rows) and a number of months (columns) and I need to break the rows out to columns while keeping the total for the month and total for the columns equal. Also, I cannot use fractions - all numbers must be whole numbers.

I am attaching an example with two scenarios that I have solved manually and with a formula. As you will see the formula has an issue with remainders. I've tried ROUND, ROUNDUP, ROUNDDOWN, and INT to get different results, but the remainder always stumps me.

If I need to take multiple steps (i.e. solve as I have and then do additional tables to resolve the remainder) I am willing to do that, I just can't see the correct formula for solving both the column and row.

Any suggestions?

Thanks in advance,

Chance2

Balance Rows and Columns.xlsx

Originally Posted by shg

Insert a column and leave blank as shown:

       --A-- -B-- C D- -E- -F- -G- -H- --I--
   3                M1 M2  M3  M4  M5  TOTAL
   4   ListA         0 650 200 300 260  1410
   5   Item1  125    0  58  18  26  23   125
   6   Item2   10    0   5   1   2   2    10
   7   Item3  125    0  58  18  26  23   125
   8   Item4  100    0  46  14  21  19   100
   9   Item5  150    0  69  21  32  28   150
  10   Item6  400    0 184  57  85  74   400
  11   Item7  300    0 138  43  64  55   300
  12   Item8  200    0  92  28  43  37   200
  13         1410    0 650 200 299 261      

In D5 and copy across and down,

=IF(D4=0, 0, ROUND(D$4*($B5 - SUM(B5:$C5)) / ($I$4 - SUM(B$4:$C$4)), 0))

shg,

Your formula is producing the correct row totals, but two of the column totals are off.

shg,

Thanks for the attempt. I ending up doing a part approach:
(1) solve the split (using the original formula)
(2) balance the rows (using LARGE and COUNTIF)
(3) balance the columns (inverted LARGE and COUNTIF)