annachiara12
New Member
- Joined
- Sep 4, 2017
- Messages
- 4
Dear all,
I have the following matrix
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>
</tbody>
where the letters are fishing gears and the numbers are regions. I would like to find the numbers inside the matrix as is:
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>
</tbody>
using the following formula
=IF(ISBLANK(A1),"", ((sum2$1*$sum1A)/$sum2$sum1))
This formula works but somehow this equation still thinks there are 7 full columns and 14 rows and therefore the sum of it is not correct (see below)
<colgroup><col><col><col span="4"><col span="2"><col></colgroup><tbody>
</tbody>
Any tips?
thank you
I have the following matrix
| A | B | C | D | E | F | G | sum2 | |
| 1 | 3745 | |||||||
| 2 | 10629 | |||||||
| 3 | 5442 | |||||||
| 4 | 14089 | |||||||
| 5 | 9204 | |||||||
| 6 | 34842 | |||||||
| 7 | 2099 | |||||||
| 8 | 10913 | |||||||
| 9 | 29621 | |||||||
| 10 | 22181 | |||||||
| 11 | 23427 | |||||||
| 12 | 3724 | |||||||
| 13 | 8055 | |||||||
| 14 | 45033 | |||||||
| sum1 | 78181 | 45698 | 30201 | 21794 | 33557 | 8425 | 5148 | 223004 |
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>
</tbody>
where the letters are fishing gears and the numbers are regions. I would like to find the numbers inside the matrix as is:
| A | B | C | D | E | F | G | sum2 | |
| 1 | 1154 | 1434 | 951 | 206 | 3745 | |||
| 2 | 2903 | 6334 | 1114 | 278 | 10629 | |||
| 3 | 3373 | 367 | 205 | 864 | 633 | 5442 | ||
| 4 | 3432 | 6102 | 151 | 4032 | 372 | 14089 | ||
| 5 | 4223 | 81 | 3964 | 936 | 9204 | |||
| 6 | 16280 | 8464 | 2203 | 1840 | 4042 | 1056 | 957 | 34842 |
| 7 | 1594 | 325 | 180 | 2099 | ||||
| 8 | 3109 | 3254 | 3793 | 757 | 10913 | |||
| 9 | 8693 | 6111 | 11544 | 2956 | 317 | 29621 | ||
| 10 | 4737 | 14881 | 962 | 1601 | 22181 | |||
| 11 | 4894 | 14936 | 2342 | 1255 | 23427 | |||
| 12 | 1074 | 487 | 632 | 1531 | 3724 | |||
| 13 | 3049 | 3777 | 1229 | 8055 | ||||
| 14 | 19666 | 1306 | 9939 | 6533 | 3398 | 4191 | 45033 | |
| sum1 | 78181 | 45698 | 30201 | 21794 | 33557 | 8425 | 5148 | 223004 |
<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>
</tbody>
using the following formula
=IF(ISBLANK(A1),"", ((sum2$1*$sum1A)/$sum2$sum1))
This formula works but somehow this equation still thinks there are 7 full columns and 14 rows and therefore the sum of it is not correct (see below)
| A | B | C | D | E | F | G | sum2 | |
| 1 | 1313 | 507 | 564 | 141 | 2525 | |||
| 2 | 3726 | 1439 | 1599 | 402 | 7167 | |||
| 3 | 1908 | 737 | 532 | 819 | 206 | 4201 | ||
| 4 | 4939 | 1908 | 1377 | 2120 | 532 | 10877 | ||
| 5 | 3227 | 1246 | 1385 | 348 | 6206 | |||
| 6 | 12215 | 7140 | 4719 | 3405 | 5243 | 1316 | 804 | 34842 |
| 7 | 736 | 205 | 316 | 1257 | ||||
| 8 | 3826 | 1478 | 1067 | 1642 | 8012 | |||
| 9 | 10385 | 6070 | 2895 | 4457 | 1119 | 24926 | ||
| 10 | 7776 | 4545 | 2168 | 3338 | 17827 | |||
| 11 | 8213 | 4801 | 2290 | 3525 | 18828 | |||
| 12 | 1306 | 504 | 364 | 560 | 2734 | |||
| 13 | 2824 | 1212 | 304 | 4340 | ||||
| 14 | 15788 | 9228 | 6099 | 6776 | 1701 | 1040 | 40632 | |
| sum1 | 78181 | 31784 | 18638 | 14301 | 33557 | 6070 | 1844 | 184375 |
<colgroup><col><col><col span="4"><col span="2"><col></colgroup><tbody>
</tbody>
Any tips?
thank you
