# Excel formula to calculate values in a matrix with fixed totals for line & colums

• Oct 6th 2012, 11:24 PM
roskammer
Excel formula to calculate values in a matrix with fixed totals for line & colums
Dear experts,

I have a question. I need a formula in excel to calculate following :

Say I have a matrix in excel 4 rows, 3 columns :
 8 5 6 19 4 7 2 13 3 1 4 8 2 3 1 7 17 16 14 47

- values in red are totals (column & row)
- green value = grand total

Now we do a reorganisation of the row totals to :
Total row 1 = 15
Total row 2 = 11
Total row 3 = 12
Total row 4 = 9
Grand total remains 47
Column totals (17, 16, 14) are FIXED too

The formula is to recalculate (pro rata) the matrix values in order that the row totals become as set above (15, 11, 12, 9),
and the column totals remain equally (17, 16, 14).

Can somebody help me for this formula ?

Thank you very much,
Roskammer
• Oct 6th 2012, 11:50 PM
chiro
Re: Excel formula to calculate values in a matrix with fixed totals for line & colums
Hey roskammer.

Just to clarify can you say what is fixed and what varies in terms of the cells (and relevant summations) please?
• Oct 7th 2012, 12:06 AM
roskammer
Re: Excel formula to calculate values in a matrix with fixed totals for line & colums
Dear chiro,

I need to recalculate cell A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4.
A5=17=SUM(A1:A4) (=fixed)
B5=16=SUM(B1:B4) (=fixed)
C5=14=SUM(C1:C4) (=fixed)

D1=15 (=set) -> changed from 19 to 15 --> must be SUM(A1:C1)
D2=11 (=set) -> changed from 13 to 11 --> must be SUM(A2:C2)
D3=12 (=set) -> changed from 8 to 12 --> must be SUM(A3:C3)
D4=9 (=set) -> changed from 7 to 9 --> must be SUM(A4:C4)

So i need to recalculate the cell mentionned above prorata to the changed row totals...
I hope I made mysef clear ?

Thank you for any help.

Rgds,
Roskammer
• Oct 7th 2012, 12:16 AM
chiro
Re: Excel formula to calculate values in a matrix with fixed totals for line & colums
The first thing to look at for this kind of thing is if a unique solution exists.

You have 12 variables which means you need 12 independent constraints. For future reference, if you need to solve n variables you need n independent constraints for a unique solution (if one exists).

Your sums give three constraints (3) in the first section, you have four constraints in the middle section (4) which gives a total of 7 individual constraints out of a needed 12.

So you are five constraints short which means you need to either add more constraints that are independent of the above 7 or you will multiple solutions.

In other words, you will need to either fix five of the cells or introduce five constraints (like sums in the way you have done above) in order to get a unique solution and every single constraint must be linearly independent to the other.

If you only have linear sums of all the variables as constraints, you can stick this in a matrix find it's inverse and get the solution to all 12 cells in a heartbeat.