Calculate matrix in excel containing numbers and empty values based on column and row totals

[annachiara12]

I have a matrix with totals rows and totals columns as follow
[TABLE="width: 576"]
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl68, width: 64"]A[/TD]
[TD="class: xl68, width: 64"]B[/TD]
[TD="class: xl68, width: 64"]C[/TD]
[TD="class: xl68, width: 64"]D[/TD]
[TD="class: xl68, width: 64"]E[/TD]
[TD="class: xl68, width: 64"]F[/TD]
[TD="class: xl68, width: 64"]G[/TD]
[TD="class: xl68, width: 64"]sum
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]3745
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]10629[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]5442[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]14089
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]9204[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]34842
[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]2099
[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]10913[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]29621[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]22181
[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]23427
[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]3724[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]8055[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl69, align: right"]45033
[/TD]
[/TR]
[TR]
[TD="class: xl66"]sum
[/TD]
[TD="class: xl69, align: right"]78181[/TD]
[TD="class: xl69, align: right"]45698[/TD]
[TD="class: xl69, align: right"]30201[/TD]
[TD="class: xl69, align: right"]21794[/TD]
[TD="class: xl69, align: right"]33557[/TD]
[TD="class: xl69, align: right"]8425[/TD]
[TD="class: xl69, align: right"]5148[/TD]
[TD="class: xl69, align: right"]223004
[/TD]
[/TR]
</tbody>[/TABLE]

where letters correspond to regions and numbers to fishing gears.
I want to calculate a value for each column within each row using the totals from the rows and the columns. The output should be this

[TABLE="width: 576"]
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl68, width: 64"] A
[/TD]
[TD="class: xl68, width: 64"] B
[/TD]
[TD="class: xl68, width: 64"] C
[/TD]
[TD="class: xl68, width: 64"] D
[/TD]
[TD="class: xl68, width: 64"] E
[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"]F G
[/TD]
[TD="class: xl68, width: 64"] sum
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1154[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]1434[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]951[/TD]
[TD="class: xl67, align: right"]206[/TD]
[TD][/TD]
[TD="class: xl67, align: right"]3745[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2903[/TD]
[TD="class: xl67"][/TD]
[TD="align: right"]6334[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]1114[/TD]
[TD="class: xl67, align: right"]278[/TD]
[TD][/TD]
[TD="class: xl67, align: right"]10629[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl67, align: right"]3373[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]367[/TD]
[TD="class: xl67, align: right"]205[/TD]
[TD="class: xl67, align: right"]864[/TD]
[TD="class: xl67, align: right"]633[/TD]
[TD][/TD]
[TD="class: xl67, align: right"]5442[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67, align: right"]3432[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]6102[/TD]
[TD="class: xl67, align: right"]151
[/TD]
[TD="class: xl67, align: right"]4032[/TD]
[TD="class: xl67, align: right"]372[/TD]
[TD][/TD]
[TD="class: xl67, align: right"]14089[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl67, align: right"]4223[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]81[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]3964[/TD]
[TD="class: xl67, align: right"]936[/TD]
[TD][/TD]
[TD="class: xl67, align: right"]9204[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67, align: right"]16280[/TD]
[TD="class: xl67, align: right"]8464[/TD]
[TD="class: xl67, align: right"]2203[/TD]
[TD="class: xl67, align: right"]1840[/TD]
[TD="class: xl67, align: right"]4042[/TD]
[TD="class: xl67, align: right"]1056[/TD]
[TD="class: xl67, align: right"]957[/TD]
[TD="class: xl67, align: right"]34842[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl67, align: right"]1594[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]325[/TD]
[TD="class: xl67, align: right"]180[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]2099[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="class: xl67, align: right"]3109[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]3254[/TD]
[TD="class: xl67, align: right"]3793[/TD]
[TD="class: xl67, align: right"]757[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]10913
[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl67, align: right"]8693[/TD]
[TD="class: xl67, align: right"]6111[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]11544[/TD]
[TD="class: xl67, align: right"]2956[/TD]
[TD="class: xl67, align: right"]317[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]29621
[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="class: xl67, align: right"]4737[/TD]
[TD="class: xl67, align: right"]14881[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]962[/TD]
[TD="class: xl67, align: right"]1601[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]22181
[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl67, align: right"]4894[/TD]
[TD="class: xl67, align: right"]14936[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]2342[/TD]
[TD="class: xl67, align: right"]1255[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]23427[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="class: xl67, align: right"]1074[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]487[/TD]
[TD="class: xl67, align: right"]632[/TD]
[TD="class: xl67, align: right"]1531[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]3724[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="class: xl67, align: right"]3049[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]3777[/TD]
[TD="class: xl67, align: right"]1229[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]8055[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="class: xl67, align: right"]19666[/TD]
[TD="class: xl67, align: right"]1306[/TD]
[TD="class: xl67, align: right"]9939[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67, align: right"]6533[/TD]
[TD="class: xl67, align: right"]3398[/TD]
[TD="class: xl67, align: right"]4191[/TD]
[TD="class: xl67, align: right"]45033
[/TD]
[/TR]
[TR]
[TD="class: xl66"]sum
[/TD]
[TD="class: xl69, align: right"]78181[/TD]
[TD="class: xl69, align: right"]45698[/TD]
[TD="class: xl69, align: right"]30201[/TD]
[TD="class: xl69, align: right"]21794[/TD]
[TD="class: xl69, align: right"]33557[/TD]
[TD="class: xl69, align: right"]8425[/TD]
[TD="class: xl69, align: right"]5148[/TD]
[TD="class: xl69, align: right"]223004
[/TD]
[/TR]
</tbody>[/TABLE]

For now I have used the following formula

Row Total*Column Total/Overall Total
which works only if each column/row has a value in it. If there are empty cells is not working correctly
To make excel understanding that there are empty cells and to exclude them from the calculation I have tried this:
=IF(ISBLANK(A1),"", ((sum$1*$sumA)/$sum$sum))
but by doing this, the formula still thinks there are missing values in the equation and doesn't return the correct sums

[TABLE="width: 712"]
<colgroup><col><col><col span="4"><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] A
[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[TD] sum
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1313[/TD]
[TD][/TD]
[TD="align: right"]507[/TD]
[TD][/TD]
[TD="align: right"]564[/TD]
[TD="align: right"]141[/TD]
[TD][/TD]
[TD="align: right"]2525
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3726[/TD]
[TD][/TD]
[TD="align: right"]1439[/TD]
[TD][/TD]
[TD="align: right"]1599[/TD]
[TD="align: right"]402[/TD]
[TD][/TD]
[TD="align: right"]7167
[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1908[/TD]
[TD][/TD]
[TD="align: right"]737[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]819[/TD]
[TD="align: right"]206[/TD]
[TD][/TD]
[TD="align: right"]4201[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4939[/TD]
[TD][/TD]
[TD="align: right"]1908[/TD]
[TD="align: right"]1377[/TD]
[TD="align: right"]2120[/TD]
[TD="align: right"]532[/TD]
[TD][/TD]
[TD="align: right"]10877
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]3227[/TD]
[TD][/TD]
[TD="align: right"]1246[/TD]
[TD][/TD]
[TD="align: right"]1385[/TD]
[TD="align: right"]348[/TD]
[TD][/TD]
[TD="align: right"]6206
[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]12215[/TD]
[TD="align: right"]7140[/TD]
[TD="align: right"]4719[/TD]
[TD="align: right"]3405[/TD]
[TD="align: right"]5243[/TD]
[TD="align: right"]1316[/TD]
[TD="align: right"]804[/TD]
[TD="align: right"]34842[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]736[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]316[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1257[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]3826[/TD]
[TD][/TD]
[TD="align: right"]1478[/TD]
[TD="align: right"]1067[/TD]
[TD="align: right"]1642[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8012[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10385[/TD]
[TD="align: right"]6070[/TD]
[TD][/TD]
[TD="align: right"]2895[/TD]
[TD="align: right"]4457[/TD]
[TD="align: right"]1119[/TD]
[TD][/TD]
[TD="align: right"]24926
[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]7776[/TD]
[TD="align: right"]4545[/TD]
[TD][/TD]
[TD="align: right"]2168[/TD]
[TD="align: right"]3338[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17827
[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]8213[/TD]
[TD="align: right"]4801[/TD]
[TD][/TD]
[TD="align: right"]2290[/TD]
[TD="align: right"]3525[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18828[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1306[/TD]
[TD][/TD]
[TD="align: right"]504[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]560[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2734[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]2824[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1212[/TD]
[TD="align: right"]304[/TD]
[TD][/TD]
[TD="align: right"]4340[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]15788[/TD]
[TD="align: right"]9228[/TD]
[TD="align: right"]6099[/TD]
[TD][/TD]
[TD="align: right"]6776[/TD]
[TD="align: right"]1701[/TD]
[TD="align: right"]1040[/TD]
[TD="align: right"]40632
[/TD]
[/TR]
[TR]
[TD]sum
[/TD]
[TD="align: right"]78181
[/TD]
[TD="align: right"]31784[/TD]
[TD="align: right"]18638[/TD]
[TD="align: right"]14301[/TD]
[TD="align: right"]33557[/TD]
[TD="align: right"]6070[/TD]
[TD="align: right"]1844[/TD]
[TD="align: right"]184375
[/TD]
[/TR]
</tbody>[/TABLE]


any idea how to solve it?
thank you

 

[micsza]

Could you possibly be more specif on this? I'm not exactly sure what is input and what you are trying to achieve

 

[annachiara12]

yes sorry! I have a matrix with just totals like the example shown in the first matrix. The second matrix is what i am trying to achieve: a matrix with inside numbers per each column and row but that in some cases (that i know) has empty cells. I have used the formula =IF(ISBLANK(A1),"", ((sum$1*$sumA)/$sum$sum)) but this formula assumes that each column/row has a number inside the matrix and not empty cells ...
Is it more clear?
thank you

 

[ryan0521]

how will you know that the value in b1 is 1313?

 

[annachiara12]

the last matrix is not correct, is the one i get if i use the formula above.
The correct number for B1 is 1154.
I know all the inside numbers for that matrix (the second matrix) because I took a matrix with known values to see if the formula is correct and in order to replicate it for other matrix with unknown inside numbers