Calculating a generalized inverse matrix

[Ying-Foon Chow]

I'd appreciate if someone can tell me if I could find a (generalized)
inverse matrix of a singular matrix using Excel. Specifically, I have
a square matrix, say 10 by 10, but I know the rank of the matrix is 8.
Still, I need to find its inverse and I think that can be done in
other programming languages, but I am not sure how to do that in Excel
(or if that can be done at all). Thanks in advance.

Regards,
Y. F.

 

[Gary''s Student]

You are in luck. If the matrix is singular, it has no inverse because its
determinant is zero.

 

[Ying-Foon Chow]

Thank you and that is why I am looking for a "generalized" inverse
matrix (a.k.a. pseudoinverse matrix?). With a search on Google, I
think an example is the Moore-Penrose Matrix Inverse (http://
mathworld.wolfram.com/Moore-PenroseMatrixInverse.html) and some
reasons for such matrix is to find "optimum" in an over/under
identified(?) system (http://www.cs.ut.ee/~toomas_l/linalg/lin2/
node15.html).

On second thought, I know Excel is not a matrix programming
environment, but it seems I need an algorithm to do singular value
decomposition, and I'd appreciate if someone could tell me if that can
be implemented in Excel (but not VBA if possible). Thanks in advance.

Regards,
Y. F.

 

[iliace]

Is MINVERSE() what you're looking for? I'm not a terribly advanced
math person, but if I had a 10-variable system of 10 equations, with
coefficients entered in A1:J10 and results in K1:K10, I would use this
to find the value of each variable:

=MMULT(MINVERSE(A1:J10),K1:K10)

Entered in a 10-cell range as an array formula (Ctrl+Shift+Enter) -
but I'm not sure that's the kind of inverse you're looking for.

 

[Ying-Foon Chow]

Thank you for the information. The matrix I faced cannot be inverted
by MINVERSE because it is not full rank (or "non invertible" in a
general sense).

While I do get a matrix by using MINVERSE, when I multiplied this
matrix with the original matrix, I am not getting the identitiy matrix
(which is expected, since the original matrix should not be invertible
in a general sense), but I am not getting the "pseudo inverse" or
"generalized inverse" either.

I think this is a problem if we are going to use Excel to do some
matrix arithmetics, especially MINVERSE: it will not give any warning.

Best regards,
Y. F.

 

[Dana DeLouis]

Hi. I've tried writing code for "PseudoInverse" in the past, but had no
luck. Have you made any progress?

If you have a particular problem, feel free to send me your workbook. I can
run "PseudoInverse" via a math program for you. Its based on
"SingularValueDecomposition."

I did find this code once, but didn't do much with it. (beginning on line
#38)

http://www.koders.com/cpp/fidB528B6A8E0A129B3218510CC20ECA5D936C05A4A.aspx

 

[Ying-Foon Chow]

Hi. I've tried writing code for "PseudoInverse" in the past, but had no
luck. Have you made any progress?

If you have a particular problem, feel free to send me your workbook. I can
run "PseudoInverse" via a math program for you. Its based on
"SingularValueDecomposition."

I did find this code once, but didn't do much with it. (beginning on line
#38)

http://www.koders.com/cpp/fidB528B6A8E0A129B3218510CC20ECA5D936C05A4A...

--
Dana DeLouis







- Show quoted text -

Thank you for your message. I think I have found a solution using the
Singular Value Decomposition as you mentioned. It seems that I have
code it up using VBA or C++, or using an Excel add-in. In fact, there
are a number of Excel add-ins that can be found when I did a search on
Google using "Singular Value Decomposition" and "Microsoft Excel"
together. Hope this is useful to those who are also trying to use
Excel for matrix operations.

Regards,
Y. F.

 

[Jerry W. Lewis]

In Excel 2003 or greater, you could avoid much of the programming by using
LINEST to implement the characterization from p.37 of Lawson & Hanson
"Solving Least Squares Problems" that the jth column of the unique
Moore-Penrose generalized inverse is the least squares solution to
A b = e[j]
where e[j] is the vector with 1 in the jth position and zeroes elsewhere.
Thus LINEST(e[j],A) gives the jth column as a row vector in reverse order.

Prior to 2003, LINEST required A to be nonsingular, so this wouldn't work.

For the benefit of those who have no clue what we are talking about, a
generalized inverse (G) of the matrix A satisfies A*G*A = A. If A is
nonsingular, then G is MINVERSE(A) and is unique; otherwise there are
infinitely many different matrices G with this property. The Moore-Penrose
generalized inverse is the unique generalized inverse that satisfies the
following four properties of an inverse
1. A*G*A = A
2. G*A*G = G
3. A*G is symmetric
4. G*A is symmetric

As an example, the 3x2 matrix ={2,1;5,2;2,1} has the 2x3 matrix
={-1,1,-1;2.5,-2,2.5} as its Moore-Penrose generalized inverse

Jerry

 

[Jerry W. Lewis]

That should have been LINEST(e[j],A,FALSE).

Sorry for any confusion,
Jerry

 

[Jerry W. Lewis]

For A of less than full column rank, the jth column must be the minimum norm
solution of the stated least squares problem. Most packages (including
LINEST) return the solution of a convenient parametrization that is rarely
minimum norm; therefore my suggestion only works when A is full column rank.
On the plus side, that means that it would work in earlier versions of Excel.

Jerry

In Excel 2003 or greater, you could avoid much of the programming by using
LINEST to implement the characterization from p.37 of Lawson & Hanson
"Solving Least Squares Problems" that the jth column of the unique
Moore-Penrose generalized inverse is the least squares solution to
A b = e[j]
where e[j] is the vector with 1 in the jth position and zeroes elsewhere.
Thus LINEST(e[j],A) gives the jth column as a row vector in reverse order.

Prior to 2003, LINEST required A to be nonsingular, so this wouldn't work.

For the benefit of those who have no clue what we are talking about, a
generalized inverse (G) of the matrix A satisfies A*G*A = A. If A is
nonsingular, then G is MINVERSE(A) and is unique; otherwise there are
infinitely many different matrices G with this property. The Moore-Penrose
generalized inverse is the unique generalized inverse that satisfies the
following four properties of an inverse
1. A*G*A = A
2. G*A*G = G
3. A*G is symmetric
4. G*A is symmetric

As an example, the 3x2 matrix ={2,1;5,2;2,1} has the 2x3 matrix
={-1,1,-1;2.5,-2,2.5} as its Moore-Penrose generalized inverse

Jerry

I'd appreciate if someone can tell me if I could find a (generalized)
inverse matrix of a singular matrix using Excel. Specifically, I have
a square matrix, say 10 by 10, but I know the rank of the matrix is 8.
Still, I need to find its inverse and I think that can be done in
other programming languages, but I am not sure how to do that in Excel
(or if that can be done at all). Thanks in advance.

Regards,
Y. F.

 

[Mary Zacheus]

First of all, you have to select a full range rectangular matrix in your square matrix. Actually, the follwing method is valid for such a kind of matrix found in linear systems with more unknowns that equations. So this matrix will have n rows and m>n columns, say n = 4 and m = 6. Assuming that you have the matrix in the C2:H5 area and the second members of the equations in A2:A5 (say), select a column with 6 elements and do:

=MMULT(TRANSPONER(C2:H5);MMULT(MINVERSA(MMULT(C2:H5;TRANSPONER(C2:H5)));A2:A5))
(change commands from spanish to english)
CNTR+SHIFT+ENTER

So you will get a solution with minimum norm.

If you really want the pseudoinverse of the C2:H5 matrix, select 6 rows and 4 columns (the same as if you would tranpose the matrix) and then do this:

=MMULT(TRANSPONER(C2:H5);MINVERSA(MMULT(C2:H5;TRANSPONER(C2:H5))))
CNTR+SHIFT+ENTER

DIXIT. MZ.

I'd appreciate if someone can tell me if I could find a (generalized)
inverse matrix of a singular matrix using Excel. Specifically, I have
a square matrix, say 10 by 10, but I know the rank of the matrix is 8.
Still, I need to find its inverse and I think that can be done in
other programming languages, but I am not sure how to do that in Excel
(or if that can be done at all). Thanks in advance.

Regards,
Y. F.

On Sunday, November 11, 2007 11:38 AM GarysStuden wrote:
You are in luck. If the matrix is singular, it has no inverse because its
determinant is zero.
--
Gary''s Student - gsnu2007a


"Ying-Foon Chow" wrote:

On Wednesday, November 28, 2007 1:14 PM post_a_repl wrote:
In Excel 2003 or greater, you could avoid much of the programming by using
LINEST to implement the characterization from p.37 of Lawson & Hanson
"Solving Least Squares Problems" that the jth column of the unique
Moore-Penrose generalized inverse is the least squares solution to
A b = e[j]
where e[j] is the vector with 1 in the jth position and zeroes elsewhere.
Thus LINEST(e[j],A) gives the jth column as a row vector in reverse order.

Prior to 2003, LINEST required A to be nonsingular, so this wouldn't work.

For the benefit of those who have no clue what we are talking about, a
generalized inverse (G) of the matrix A satisfies A*G*A = A. If A is
nonsingular, then G is MINVERSE(A) and is unique; otherwise there are
infinitely many different matrices G with this property. The Moore-Penrose
generalized inverse is the unique generalized inverse that satisfies the
following four properties of an inverse
1. A*G*A = A
2. G*A*G = G
3. A*G is symmetric
4. G*A is symmetric

As an example, the 3x2 matrix ={2,1;5,2;2,1} has the 2x3 matrix
={-1,1,-1;2.5,-2,2.5} as its Moore-Penrose generalized inverse

Jerry

"Ying-Foon Chow" wrote:

On Wednesday, November 28, 2007 1:19 PM post_a_repl wrote:
That should have been LINEST(e[j],A,FALSE).

Sorry for any confusion,
Jerry

"Jerry W. Lewis" wrote: