Inverting a Matrix

[TampaBridgeDesign]

I'm trying to write a macro that will invert a square matrix of any random size. Say I have a 10 x 10 matrix, how do I word the macro such that it selects from A1:J10 and then inverts that matrix? I already have an ad-in that will allow me to invert larger matrices than Excel normally allows. Thanks!

 

[cowski]

See this thread:
thread764-29458

 

[Denial]

I would do it as a user-defined array function. The bones of it could then look like this.

Public Function MatInverter(Uninverted)
Dim Inverted as Variant
MatRows = Uninverted.Rows.Count
MatCols = Ininverted.Columns.Count
' Test that MatRows equals MatCols
     ...  Code to your taste
ReDim Inverted(1 to MatRows, 1 to MatCols)
' Do the inversion
     ...  Algorithm and code to your taste
Matinverter = Inverted
End Function

Put it in a module. There may well be some upper limit on the size that it can handle, so test that aspect out before you use it under fire.

 

[GregLocock]

any particualr reason you don't like MINVERSE ?

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TampaBridgeDesign]

I will ultimately be using MINVERSE. The problem is selecting a random size square matrix. For one run I might have a 64 x 64 matrix but for another run I might have a 24 x 24 matrix. If I generically set it up to select the 64 x 64 matrix (A1:BL64) then when I run it with the 24 x 24 matrix I get #VALUE! instead of the new inverted 24 x 24 matrix.

 

[Denial]

if you want to be able to change the size of the matrix without changing the "structure" of the spreadsheet, the skeleton code I gave above will need to be modified.

Public Function MatInverter(Uninverted, Size)
Dim Inverted as Variant
MatRows = Uninverted.Rows.Count
MatCols = Ininverted.Columns.Count
' Test that MatRows equals MatCols
     ...  Code to your taste
' Test that Size is within the limits.
     ...  Code to your taste
ReDim Inverted(1 to MatRows, 1 to MatCols)
' Do the inversion by restricting yourself
' to rows 1 to Size and columns 1 to Size.
     ...  Algorithm and code to your taste
' Fill rows Size+1 to MatRows and columns Size+1 to MatCols
' with some sort of null.
     ...  Code to your taste
Matinverter = Inverted
End Function

So you would set up your spreadsheet as if for the largest envisaged matrix and corresponding resulting inverse. Then you ensure that your actual matrix occupies the top left hand corner of the megamatrix. Somewhere else on the spreadsheet you have a cell that contains the used size of this megamatrix, either by direct user entry or by some form of automatic examination of the megamatrix. It is this cell that is passed to the function as parameter "Size".

Alternatively, you may be able to use my original approach provided you can find an inversion algorithm that is not sensitive to large blocks of zeroes.

 

[TampaBridgeDesign]

I guess I've never created a function before in Visual Basic. So I just enter the code that you have provided. Then lets assume the largest possible square matrix I will have is a 64 x 64 which would be A1:BL64. Then lets say I want to find the inverse of a 4 x 4 matrix using the function you've provided. Would I just go into A1 and then say "=MatInverter(A1:BL64,4)" or "=MatInverter(A1:BL64,A14)"? Thanks!

 

[Denial]

(1) Select the 64x64 block of cells into which you want the inverted matrix placed, say A71 to BL134.
(2) Type the formula, ie "=MatInverter(A1:BL64,A66)" where A66 is the cell containing the used size of the megamatrix.
(3) When you have completed typing the formula, use <Ctrl-Shift-Enter> instead of merely <Enter>. This tells Excel that the formula you have just typed is to be treated as an "array formula".
(4) Now when you place your cursor anywhere inside A71:BL134 the formula will appear inside {braces}. This is Excel's way of telling you that it is an array formula.

All of this is the same as it would be if you were using one of Excel's standard array formulae, such as MINVERSE.

 

[m777182]

If you want to write a piece of VBa code then dimension a n*n array A and a n*n array B. Make all non diagonal elements in B equal zero and all diagonal elements equal 1.
Transfer your data into matrix A and perform Gaussian elimination of lower triangular and upper triangular matrix on A and B as they were a single n*2n matrix.
At the end you will get an inverse in matrix B.
m777182

 

[TampaBridgeDesign]

How do I make this generic:

__________________________________________________________
first = -8

Range("A912").Select
Selection.FormulaArray = "=MINVERSE(R[first]C:R[-5]C[3])"
__________________________________________________________

What I have doesn't work. I want to make the number in the brackets a variable. Can you do this? My guess is no but you obviously have more experience than I do.

 

[Dinosaur]

Could you name the region, like "GlobalK", and then insert the name into your macro? or is this not possible with this function?