MINVERSE errors

[Oliver Heaviside]

regardez vous sil vou plait this result of multiplying a7by7 matrix by its
MINVERSE "inverse":
1.0E+00 3.7E-08 2.3E-06 1.4E-04 8.2E-03 4.9E-01 2.9E+01
1.0E-10 1.0E+00 2.3E-07 1.1E-05 5.9E-04 3.5E-02 1.6E+00
2.4E-11 1.3E-09 1.0E+00 3.7E-06 2.1E-04 1.1E-02 6.6E-01
-8.4E-13 -4.3E-11 -2.4E-09 1.0E+00 -7.5E-06 -3.8E-04 -2.2E-02
1.2E-14 5.4E-13 3.3E-11 1.5E-09 1.0E+00 4.4E-06 2.7E-04
-9.7E-17 -4.7E-15 -2.8E-13 -1.3E-11 -7.0E-10 1.0E+00 -1.8E-06
4.2E-19 2.0E-17 1.3E-15 6.0E-14 3.2E-12 1.3E-10 1.0E+00
see the top right hand term 29!!! the curve fitted from this does not fit!

in the help at
http://office.microsoft.com/en-us/excel/HP052091791033.aspx?pid=CH062528291033
it says of MINVERSE
The size of the array must not exceed 52 columns by 52 rows. If it does, the
function returns a #VALUE! error.

MDETERM gives -3.25223E+110
for this 10 by 10 matrix, the first 7by7 of which was referred to above
9.0E+00 2.8E+02 1.2E+04 5.5E+05 2.7E+07 1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+1
2.8E+02 1.2E+04 5.5E+05 2.7E+07 1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+1
1.2E+04 5.5E+05 2.7E+07 1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+1
5.5E+05 2.7E+07 1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+2
2.7E+07 1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+2
1.4E+09 7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+23 7.5E+2
7.5E+10 4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+23 7.5E+24 4.3E+2
4.1E+12 2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+23 7.5E+24 4.3E+26 2.5E+2
2.2E+14 1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+23 7.5E+24 4.3E+26 2.5E+28 1.4E+3
1.3E+16 7.0E+17 4.0E+19 2.3E+21 1.3E+23 7.5E+24 4.3E+26 2.5E+28 1.4E+30 8.4E+31

is MINVERSE working as intended, not with 52 by 52 but 7 by 7?
is this why the polynomial fitted to xy plots is 6th order no bigger?

can anyone advise how to invert nonsingular matrices, up to 52 by 52,
eg 10 by 10 would be good, so the product of the matrix and its
inverse looks just like this?

1000000
0100000
0010000
0001000
0000100
0000010
0000001

and no 29s or such anywhere off diagonal please?
thanks
jim in sydney

 

[Harlan Grove]

Oliver Heaviside wrote...

regardez vous sil vou plait this result of multiplying a7by7 matrix by its
MINVERSE "inverse":

....

Don't attempt French (or any language other than English) unless you
spell it correctly.

for this 10 by 10 matrix, the first 7by7 of which was referred to above:

....

You mean the top-left 7-by-7 minor of the 10-by-10 matrix?

Your problem is lack of precision. The values in your matrix span too
many orders of magnitude to calculate the inverse accurately. You need
to rescale in order to reduce the number of orders of magnitude of the
values in the matrix.

 

[Dana DeLouis]

... how to invert nonsingular matrices

... so the product of the matrix and its
...inverse looks just like this?

1000000
0100000
0010000

I'm not too sure on the question, but if x refers to a matrix, say 7-by-7,
then

=MMULT(x,MINVERSE(x))

returns an "Identity" matrix.
(Select 7-by-7 area, and Ctrl+Shift+Enter)

 

[Harlan Grove]

Dana DeLouis wrote...
....

I'm not too sure on the question, but if x refers to a matrix, say 7-by-7,
then

=MMULT(x,MINVERSE(x))

returns an "Identity" matrix.

....

It should, but try it with the OP's 10-by-10 matrix as x and Excel
returns

1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.04 2.38 148.00
0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 -0.09 -6.50
0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 -0.08
0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00

with values formatted as 0.00. Hard to ignore the 148 entry at (1,10).

 

[Harlan Grove]

Jerry W. Lewis wrote...

The condition number (ratio of largerst to smallest eigenvalues
http://en.wikipedia.org/wiki/Condition_number) for this matrix is 6.18E+31,
indicating that inverting it is a very difficult numerical problem.

Oh, to have eigenvalues and eigenvectors in Excel . . .

And for people skeptical of Wikipedia, there's always MathWorld,

http://mathworld.wolfram.com/ConditionNumber.html

Maybe MSFT just can't stomach the license for LAPACK. Or maybe their
Excel programmers insist on reinventing numerical wheels, progressing
(slowly) from freshman level efforts.