Minverse

[Albert]

Hello!
I have made a very cool econometric interface for Excel. However, I have a
little problem: some advanced Panel Data techniques use MANY variables, and
large matrixes. Some of these matrixes have to be inverted, and I am sadly
surprised to see that the Minverse function can only work with a Matrix of up
to 52x52. Does someone have (or can help me make) a function to replace the
built in one, but that can work with larger matrixes?
I thank you in advance,
Albert C.

 

[paul.robinson]

Hello!
I have made a very cool econometric interface for Excel. However, I have a
little problem: some advanced Panel Data techniques use MANY variables, and
large matrixes. Some of these matrixes have to be inverted, and I am sadly
surprised to see that the Minverse function can only work with a Matrix of up
to 52x52. Does someone have (or can help me make) a function to replace the
built in one, but that can work with larger matrixes?
I thank you in advance,
Albert C.

Hi
finding the inverse of a large matrix is a delicate business
numerically, and you run up against rounding problems in doing the
arithmetic. I'm guessing that is why there is a 52 by 52 limit (could
be wrong on that).
I'm not sure I would trust excel to calculate the inverse for very
large matrices and would go with specialised numerical methods
software. You might be able to call some C code function or some such
that reads in and outputs an array that you can work with. Try
googling on "C code matrix inverse"
Any postgrad engineers out there who had to do that kind of thing?

regards
Paul

 

[Jerry W. Lewis]

You may be able to break up the inversion problem into more manageable
chunks. This may help with the numerical issues as well.

If you can partition your matrix into submatrices such that
M = / A B \
\ C D /
where A and D are both nonsingular, then
M^-1 = / A^-1 + A^-1*B*Z^-1*C*A^-1 -A^-1*B*Z^-1 \
\ -Z^-1*B*A^-1 Z^-1 /

where Z = D - C*A^-1*B

Here * refers to MMULT and ^-1 refers to MINVERSE in order to keep the
formula size manageable.

Much simplification occurs if you can arrange it so that B and/or C is a
matrix of zeros.

Jerry

 

[Jerry W. Lewis]

Laurent Longre's MoreFunc.xll includes a MINVERSE.EXT function that will
invert larger matrices
http://xcell05.free.fr/
but it is not unlimited and you still might want to consider a partitioned
layout to possibly reduce the numerical problems.

Jerry

 

[ilia]

Interestingly enough, Excel 2007 seems to have removed this limit (I
don't remember reading about it). I just solved a 1000-variable
simultaneous equation, with answers in column 1002. Adapting this
formula to Excel 2003, using:

=MMULT(MINVERSE($A$1:$IT$254),$IV$1:$IV$254)

returns #VALUE in Excel 2003, but works in Excel 2007.