Oct 29 2020 05:12 AM
Hi there,
I have a 19 by 19 matrix that I used MINVERSE to get the inverse of.
Then I checked it in MATLAB, and there was a reasonable difference in the solution?
Any ideas on why?
The original matrix and then EXCEL and MATLAB solutions are below
Original Matrix
-1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 |
0 | 1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 |
0 | 0 | 1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 |
0 | 0 | 0 | 1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 |
0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | -1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 1 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | -1 | 0 | 0 | -1 | 1 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 |
-276316 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1375046 | 2650094 | 0 | 0 | 0 | 0 | -1111979 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 373532 | 0 | -2650094 | 0 | 0 | 0 | -357614 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2803412 | 0 | 0 | 0 | 1111979 | 0 | -82199 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 179412 | 0 | 0 | 0 | -2803412 | 0 | 0 | 357614 | 0 | 1385295 | 0 | 0 | 0 |
0 | 0 | -1363008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -170803 | 0 | 0 | 0 | 0 | 82199 | 0 | -628487 |
0 | 0 | 0 | 0 | 0 | -10154 | 0 | 0 | 0 | 0 | 0 | 170803 | 0 | 0 | 0 | -1385295 | 0 | 796588 | 0 |
0 | 0 | 0 | -167244 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -104345 | 0 | 0 | 0 | 0 | 0 | 628487 |
0 | 0 | 0 | 0 | -2478245 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 104345 | 0 | 0 | 0 | 0 | -796588 | 0 |
EXCEL Solution
-0.684873224 | 0.21555 | 0.023140622 | 0.001473723 | -0.000792684 | -0.0532033 | -0.05409617 | -0.07847283 | -0.189343749 | -0.020915724 | 0.012304799 | -3.60373E-07 | -2.96818E-07 | -1.4772E-07 | -1.3587E-07 | -1.58964E-08 | -8.79372E-08 | -1.35515E-08 | -2.11483E-08 |
-0.422655569 | -0.463473 | 0.052962245 | 0.003618717 | -0.001472873 | -0.1119023 | -0.11378111 | -0.16500718 | -0.219533784 | -0.214911063 | 0.025421414 | -1.4772E-07 | -1.45976E-07 | -3.71251E-07 | -2.85522E-07 | -3.62019E-08 | -1.85037E-07 | -3.04441E-08 | -4.45595E-08 |
-0.074893297 | -0.079286 | -0.12643974 | 0.104737786 | 0.038273017 | -0.0376085 | -0.03839879 | -0.04657976 | -0.053035045 | -0.056706366 | -0.083049886 | -1.58964E-08 | -1.72817E-08 | -3.62019E-08 | -4.55989E-08 | -5.64063E-07 | -7.78311E-08 | -3.97412E-07 | -3.06191E-08 |
-0.042210009 | -0.045955 | -0.085608802 | -0.62728504 | 0.139326188 | -0.0099968 | -0.01073604 | -0.01800754 | -0.023576066 | -0.027170281 | -0.055444241 | -1.35515E-08 | -1.49078E-08 | -3.04441E-08 | -4.05297E-08 | -3.97412E-07 | -7.28031E-08 | -1.3955E-06 | -6.02535E-08 |
0.086992785 | 0.081149 | 0.023109153 | -0.01862488 | -0.028701913 | 0.14029016 | 0.13867362 | 0.12522532 | 0.116072625 | 0.107182026 | 0.021963255 | -2.11483E-08 | -2.45031E-08 | -4.45595E-08 | -7.49578E-08 | -3.06191E-08 | -1.59207E-07 | -6.02535E-08 | -3.35321E-07 |
0.374558214 | 0.35026 | 0.109244439 | 0.003160031 | -0.009015838 | -0.4035704 | 0.58966894 | 0.53357286 | 0.495475951 | 0.458232251 | 0.100432211 | -8.79372E-08 | -1.01991E-07 | -1.85037E-07 | -3.12667E-07 | -7.78311E-08 | -6.65831E-07 | -7.28031E-08 | -1.59207E-07 |
-0.474781577 | -0.437239 | -0.065337586 | -0.00318594 | 0.003592398 | 0.1893561 | 0.19253082 | -0.72053033 | -0.661609025 | -0.603649039 | -0.045616014 | 1.3587E-07 | 1.57741E-07 | 2.85522E-07 | 4.84577E-07 | 4.55989E-08 | 3.12667E-07 | 4.05297E-08 | 7.49578E-08 |
-0.297199859 | -0.215185 | -0.025047158 | -0.00149201 | 0.001001224 | 0.0617259 | 0.06276148 | 0.09106204 | -0.705337622 | -0.047451433 | -0.014468596 | 2.96818E-07 | 5.45068E-07 | 1.45976E-07 | 1.57741E-07 | 1.72817E-08 | 1.01991E-07 | 1.49078E-08 | 2.45031E-08 |
-0.315126776 | -0.21555 | -0.023140622 | -0.00147372 | 0.000792684 | 0.05320328 | 0.05409617 | 0.07847283 | 0.189343749 | 0.020915724 | -0.012304799 | 3.60373E-07 | 2.96818E-07 | 1.4772E-07 | 1.3587E-07 | 1.58964E-08 | 8.79372E-08 | 1.35515E-08 | 2.11483E-08 |
-0.017926917 | -0.000366 | 0.001906536 | 1.8286E-05 | -0.00020854 | -0.0085226 | -0.00866531 | -0.01258921 | -0.105318629 | 0.068367157 | 0.002163797 | 6.35555E-08 | -2.4825E-07 | 1.74436E-09 | -2.18709E-08 | -1.38535E-09 | -1.40537E-08 | -1.35626E-09 | -3.35482E-09 |
-0.102562855 | -0.099289 | 0.01237534 | -0.00043278 | -0.002119525 | -0.0774538 | -0.07874971 | -0.11446249 | -0.118857191 | -0.181439899 | 0.020194599 | 1.185E-08 | -1.17652E-08 | 8.57285E-08 | -1.99055E-07 | -9.39695E-09 | -1.2763E-07 | -1.00856E-08 | -3.03982E-08 |
0.550548489 | 0.570455 | 0.764315821 | 0.101577754 | 0.047288855 | 0.36596191 | 0.37193227 | 0.41984738 | 0.451489003 | 0.485061384 | 0.816517903 | 7.20409E-08 | 8.47092E-08 | 1.48835E-07 | 2.67068E-07 | -4.86232E-07 | 5.88E-07 | -3.24609E-07 | 1.28588E-07 |
-0.129202794 | -0.127104 | -0.108717955 | -0.60866016 | -0.8319719 | -0.150287 | -0.14940966 | -0.14323285 | -0.13964869 | -0.134352307 | -0.077407496 | 7.59676E-09 | 9.59533E-09 | 1.41154E-08 | 3.44281E-08 | -3.66793E-07 | 8.64042E-08 | -1.33525E-06 | 2.75068E-07 |
-0.177581717 | -0.222054 | -0.040290428 | -0.00169393 | 0.002591175 | 0.12763021 | 0.12976934 | 0.18840763 | 0.043728597 | -0.556197606 | -0.031147418 | -1.60948E-07 | -3.87327E-07 | 1.39547E-07 | 3.26836E-07 | 2.83171E-08 | 2.10676E-07 | 2.56219E-08 | 5.04547E-08 |
-0.262217655 | -0.320977 | -0.029821624 | -0.00214499 | 0.000680189 | 0.05869903 | 0.05968494 | 0.08653435 | 0.030190035 | 0.193995339 | -0.013116615 | -2.12653E-07 | -1.50842E-07 | 2.23531E-07 | 1.49652E-07 | 2.03055E-08 | 9.70993E-08 | 1.68926E-08 | 2.34113E-08 |
-0.100223363 | -0.086979 | 0.043906853 | -2.5907E-05 | -0.00542344 | -0.2142143 | -0.21780024 | -0.18695747 | -0.166133074 | -0.145416788 | 0.054816197 | 4.79327E-08 | 5.57499E-08 | 1.00486E-07 | 1.71911E-07 | -3.22322E-08 | -3.53164E-07 | -3.22734E-08 | -8.42495E-08 |
-0.347762272 | -0.384187 | -0.820598015 | -0.10111907 | -0.039745891 | -0.0742938 | -0.07538232 | -0.11842742 | -0.166498739 | -0.158204697 | 0.1084713 | -1.31824E-07 | -1.28694E-07 | -3.35049E-07 | -2.39924E-07 | 5.27861E-07 | -1.07205E-07 | 3.66968E-07 | -1.39405E-08 |
-0.287565429 | -0.269111 | -0.086135286 | -0.02178491 | -0.019686075 | -0.4561394 | -0.45099532 | -0.40834754 | -0.379403327 | -0.351050225 | -0.078468956 | 6.6789E-08 | 7.74879E-08 | 1.40477E-07 | 2.37709E-07 | 4.7212E-08 | 5.06624E-07 | 1.25496E-08 | -1.76114E-07 |
-0.032683288 | -0.033331 | -0.040830939 | -0.26797718 | -0.10105317 | -0.0276117 | -0.02766275 | -0.02857223 | -0.029458979 | -0.029536085 | -0.027605645 | -2.34487E-09 | -2.37397E-09 | -5.75782E-09 | -5.06922E-09 | -1.66651E-07 | -5.028E-09 | 9.98087E-07 | 2.96345E-08 |
MATLAB Solution
-0.84234 | 0.042873 | 0.042873 | 0.002822 | -0.00134 | -0.09489 | -0.09649 | -0.13995 | -0.27114 | -0.10099 | 0.021776 | -4.15E-07 | -3.51E-07 | -2.86E-07 | -2.42E-07 | -2.94E-08 | -1.57E-07 | -2.49E-08 | -3.77E-08 |
-0.81841 | -0.89745 | 0.102553 | 0.007007 | -0.00285 | -0.21668 | -0.22032 | -0.31951 | -0.42509 | -0.41614 | 0.049225 | -2.86E-07 | -2.83E-07 | -7.19E-07 | -5.53E-07 | -7.01E-08 | -3.58E-07 | -5.90E-08 | -8.63E-08 |
-0.11348 | -0.1216 | -0.1216 | 0.105068 | 0.038139 | -0.04783 | -0.04879 | -0.06165 | -0.07308 | -0.07633 | -0.08073 | -2.94E-08 | -3.06E-08 | -7.01E-08 | -7.17E-08 | -5.67E-07 | -9.47E-08 | -4.00E-07 | -3.47E-08 |
-0.07466 | -0.08154 | -0.08154 | -0.62701 | 0.139213 | -0.01859 | -0.01947 | -0.03068 | -0.04043 | -0.04367 | -0.05349 | -2.49E-08 | -2.61E-08 | -5.90E-08 | -6.25E-08 | -4.00E-07 | -8.70E-08 | -1.40E-06 | -6.37E-08 |
0.039492 | 0.029061 | 0.029061 | -0.01822 | -0.02887 | 0.127714 | 0.125886 | 0.106681 | 0.0914 | 0.083029 | 0.02482 | -3.77E-08 | -4.09E-08 | -8.63E-08 | -1.07E-07 | -3.47E-08 | -1.80E-07 | -6.37E-08 | -3.40E-07 |
0.177309 | 0.133961 | 0.133961 | 0.004849 | -0.0097 | -0.45579 | 0.536568 | 0.456565 | 0.393021 | 0.357935 | 0.112296 | -1.57E-07 | -1.70E-07 | -3.58E-07 | -4.46E-07 | -9.47E-08 | -7.52E-07 | -8.70E-08 | -1.80E-07 |
-0.17041 | -0.10348 | -0.10348 | -0.00579 | 0.004653 | 0.26994 | 0.274468 | -0.6017 | -0.50352 | -0.44888 | -0.06392 | 2.42E-07 | 2.63E-07 | 5.53E-07 | 6.90E-07 | 7.17E-08 | 4.46E-07 | 6.25E-08 | 1.07E-07 |
-0.14159 | -0.04455 | -0.04455 | -0.00282 | 0.001544 | 0.102925 | 0.104653 | 0.151813 | -0.62451 | 0.031673 | -0.02383 | 3.51E-07 | 5.99E-07 | 2.83E-07 | 2.63E-07 | 3.06E-08 | 1.70E-07 | 2.61E-08 | 4.09E-08 |
-0.15766 | -0.04287 | -0.04287 | -0.00282 | 0.001341 | 0.094895 | 0.096488 | 0.13995 | 0.271136 | 0.100986 | -0.02178 | 4.15E-07 | 3.51E-07 | 2.86E-07 | 2.42E-07 | 2.94E-08 | 1.57E-07 | 2.49E-08 | 3.77E-08 |
-0.01607 | 0.001674 | 0.001674 | 2.37E-06 | -0.0002 | -0.00803 | -0.00816 | -0.01186 | -0.10435 | 0.069313 | 0.002052 | 6.42E-08 | -2.48E-07 | 3.38E-09 | -2.06E-08 | -1.23E-09 | -1.32E-08 | -1.22E-09 | -3.16E-09 |
-0.01118 | 0.000924 | 0.000924 | -0.00122 | -0.0018 | -0.05326 | -0.05415 | -0.07878 | -0.07139 | -0.13497 | 0.014698 | 4.38E-08 | 1.98E-08 | 1.66E-07 | -1.37E-07 | -1.57E-09 | -8.76E-08 | -3.50E-09 | -2.08E-08 |
0.709207 | 0.744435 | 0.744435 | 0.100219 | 0.047842 | 0.407968 | 0.414644 | 0.481789 | 0.533899 | 0.565736 | 0.806975 | 1.27E-07 | 1.40E-07 | 2.88E-07 | 3.74E-07 | -4.73E-07 | 6.57E-07 | -3.13E-07 | 1.45E-07 |
-0.11416 | -0.1106 | -0.1106 | -0.60879 | -0.83192 | -0.1463 | -0.14536 | -0.13736 | -0.13183 | -0.1267 | -0.07831 | 1.29E-08 | 1.48E-08 | 2.73E-08 | 4.46E-08 | -3.66E-07 | 9.30E-08 | -1.33E-06 | 2.77E-07 |
-0.02882 | -0.05893 | -0.05893 | -0.00297 | 0.00311 | 0.167015 | 0.169816 | 0.246483 | 0.120996 | -0.48056 | -0.04009 | -1.09E-07 | -3.36E-07 | 2.70E-07 | 4.27E-07 | 4.11E-08 | 2.76E-07 | 3.63E-08 | 6.61E-08 |
-0.02393 | -0.05968 | -0.05968 | -0.00419 | 0.001511 | 0.121787 | 0.123832 | 0.179562 | 0.153959 | 0.315158 | -0.02745 | -1.29E-07 | -6.85E-08 | 4.33E-07 | 3.11E-07 | 4.07E-08 | 2.01E-07 | 3.41E-08 | 4.85E-08 |
0.006895 | 0.030484 | 0.030484 | -0.00094 | -0.00505 | -0.18585 | -0.18896 | -0.14514 | -0.11049 | -0.09095 | 0.048373 | 8.54E-08 | 9.27E-08 | 1.95E-07 | 2.44E-07 | -2.31E-08 | -3.06E-07 | -2.46E-08 | -7.30E-08 |
-0.70493 | -0.77584 | -0.77584 | -0.09806 | -0.04099 | -0.16886 | -0.17153 | -0.25787 | -0.35201 | -0.33981 | 0.129954 | -2.57E-07 | -2.52E-07 | -6.49E-07 | -4.81E-07 | 4.97E-07 | -2.64E-07 | 3.41E-07 | -5.16E-08 |
-0.13782 | -0.1049 | -0.1049 | -0.02307 | -0.01916 | -0.41649 | -0.41068 | -0.34988 | -0.30162 | -0.27491 | -0.08748 | 1.19E-07 | 1.29E-07 | 2.72E-07 | 3.39E-07 | 6.00E-08 | 5.72E-07 | 2.33E-08 | -1.60E-07 |
-0.03882 | -0.04006 | -0.04006 | -0.26792 | -0.10107 | -0.02924 | -0.02932 | -0.03097 | -0.03265 | -0.03266 | -0.02724 | -4.49E-09 | -4.49E-09 | -1.11E-08 | -9.22E-09 | -1.67E-07 | -7.72E-09 | 9.98E-07 | 2.90E-08 |
Oct 29 2020 05:36 AM
Some of the matrix entries have formulas that refer indirectly to column I.
Column I contains circular references, preventing the worksheet from being recalculated properly.
If you select P2:AH20, copy then paste as values to get rid of the formulas, the inverse matrix will be recalculated correctly and show the same result as MATLAB.
Oct 29 2020 08:00 AM
@Hans Vogelaar thank you for your reply, much appreciated. I got the matrix inverse to recalculate to be the same as MATLAB. At least for my own sanity, I'm glad I wasn't doing something completely wrong.
If I may, can I ask a further question.
I do understand what you are saying as regards the column I, and the circular reference. What is continuing to confuse me is that the values in the I column eventually give the values in the matrix that I was trying to get the inverse of. The matrix is not backward dependent on them, it is taking the values from column M and F, so why is the column I causing this difference?
thanks you
Gerard
Oct 29 2020 08:11 AM
The formulas in the matrix refer to columns M and F.
The formulas in column M refer to columns E and K.
The formulas in column K refer to columns C, D and J.
The formulas in column J refer to column I.
So the matrix formulas refer indirectly to column I, the column with the circular references.
Oct 29 2020 08:44 AM
@Hans Vogelaar thank you for the reply.
I guess what I confused about is the circular reference.
If the formula in column I returns a numerical result, regardless of it been a circular reference, I don't understand how it propagates through to the matrix, as there is a numerical value
Reading around this, enabling iterative calculation seems like an option.
Thanks you for your time and expertise.
Gerard