Calculate matrix of values based on column and row totals

Question

I have data that is broken into group totals (rows) and month totals (columns). I need to take the totals and calculate a value for each month within each group. From my screen shot I can find an monthly cell value for the quarter total with the formula =INT($F3/3); this would ensure that all rows total correctly but does not address the requirement for all columns to total correctly.

For the screen shot I manually added the values to visualize the desired outcome

Group Calculation: (Jan+Feb+Mar)=Q1 group total

Month Calculation: (Group1...Group6)+Inventory = Monthly Total

In the "current result" screen shot the values total correctly by groups however the result is incorrect by month. For example 'Jan' totals to 159.

Current Result

I'm looking for assistance in a formula or vba that would allow the monthly values to total to the group and month total.

Desired Result

Answer 1

It sounds like you are looking to recreate a matrix from the totals of its rows and columns.

First, you need to remove the Inventory totals from the column totals, as those figures do not need to be calculated.

Then, you can get quite close to the answer with the formula =ROUND(RowTotal*ColumnTotal/OverallTotal,0). In this case, the formula in cell B2 would be =ROUND((B$8*$E2)/$E$8,0)

However, since there is rounding involved, you will notice that the values do not add up perfectly. For a matrix this small, your best bet would be to manually fix the problem values. To do so, add formulas to check which columns and rows are improperly totaled. Your check formulas should subtract the totals of each row and column from the desired row and column total. This reveals that cell B3 is the problem, as both column B and row 3 are misaligned.

You can change cell B3 to 48, which will then ensure that all columns and rows total properly. If you had a much larger matrix to solve, you could write some VBA code to automate the check of each column and row.