SOLVED

Need help with finding the correct inverse

Copper Contributor

Hi!

I have a 7x7-matrix (A) with large numbers. 

I used the MINVERT(matrix) formula to find A:s inverse A^(-1). 

The problem I got is that the inverse is wrong. When using MMULT(matrix1; matrix2) I'm not getting the unit-matrix, unfortunately.

How come and how can I get the correct inverse? 

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

I suspect (since I don't have your sheet nor example numbers of what you have) that the issue is with round-off / resolution issues. Excel is good but doesn't have infinite capacity to represent numbers, especially when you end up with irrational or even simple fractional values. So you have 7x7 matrix with "large numbers" and I suspect the MINVERT matrix has numbers requiring more digits of resolution than is possible with excel. Hence when you try MMULT() the result isn't exactly a unit-matrix. I suspect the diagonal is very close to 1 and the non-diagonals are close to 0 but yeah, there will be some error.
As for how to get the "correct" inverse, you may need a package that can represent fractions. For example if you have 2/3 that will be 0.666666 but at some point it will have to cut it off and round that last digit up to 7. A package like Mathmatica can give you a solution in the form of 2/3 instead of 0.66666...7

Thanks!
I am having the same problem with a 4x4. In all cases I am using a symmetric matrix. In some cases I get an inverse. In my case of interest I get an asymmetric matrix. When I multiply the two instead of getting the identity matrix I get [1,0,0,0],[0,0,0,0],[0,0,0,0],[0,0,0,0]. That is a real strong indicator there is an error in the routine.
Never mind, det = 0

@Dennis365 

If you get the same problem if det is not zero I recommend using python instead. 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

I suspect (since I don't have your sheet nor example numbers of what you have) that the issue is with round-off / resolution issues. Excel is good but doesn't have infinite capacity to represent numbers, especially when you end up with irrational or even simple fractional values. So you have 7x7 matrix with "large numbers" and I suspect the MINVERT matrix has numbers requiring more digits of resolution than is possible with excel. Hence when you try MMULT() the result isn't exactly a unit-matrix. I suspect the diagonal is very close to 1 and the non-diagonals are close to 0 but yeah, there will be some error.
As for how to get the "correct" inverse, you may need a package that can represent fractions. For example if you have 2/3 that will be 0.666666 but at some point it will have to cut it off and round that last digit up to 7. A package like Mathmatica can give you a solution in the form of 2/3 instead of 0.66666...7

View solution in original post