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:

Please Login or Register  to view this content.

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)