| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MINVERSE errors
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/ex...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+16 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+17 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+19 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+21 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+23 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+24 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+26 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+28 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+30 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MINVERSE errors
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MINVERSE errors
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). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MINVERSE errors
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. Without question, MINVERSE could be improved by switching from Gaussian elimination to singular value decomposition, but that would not necessarily provide acceptable results for a numerical problem this difficult. The basis for the chart trendline restriction to no more than a 6th degree polynomial is probably pragmatic rather than numeric--in most instances a higher degree polynomial is over-fitting the data. Actually, the chart trendline is quite good numerically; better than LINEST in Excel 2003, and much much better than LINEST in versions prior to 2003. The chart trendline probably uses a singular value decomposition instead of inverting X'X; it is actually more accurate than standard statistics packages for fitting numerically difficult polynomials except where those stat packages employ orthogonal polynomial techniques. Prior to Excel 2003, LINEST did invert X'X, yet LINEST would attempt to fit up to 16th degree polynomials, even though it could not do so accurately. Jerry "Oliver Heaviside" wrote: 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/ex...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+16 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+17 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+19 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+21 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+23 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+24 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+26 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+28 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+30 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MINVERSE errors
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting the number of errors in a worksheet | Excel Worksheet Functions | |||
| Tracking Errors | Excel Worksheet Functions | |||
| strange error calculations provides errors until i add any new one | Excel Worksheet Functions | |||
| Shared sheet errors | Excel Discussion (Misc queries) | |||
| Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
Linear Mode
