# macro help with mmult and inverse

#### ssaifan

##### Active Member
i have data in 2 columns.....
in the range c7 to d.....

the number of rows of data keep on changing as i change my stock.....

now i want ....a macro to the following :

take the matrix X ie data from C7 to wherever it ends.....

and do the foll matrix multiplication calc,.......

X * (X'X)inverse * X'

where X' is transpsoe of X .......

pls help....

sam

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### martinee

##### Well-known Member
What happens if (X'X)inverse doesn't exist? Just a thought.

#### ssaifan

##### Active Member
i need that in the calc ...
u can do a 2 step process if u want ..
but pls help...

#### martinee

##### Well-known Member
Take a look at this. The transpose was created by copy/pastespecial | Transpose

Then look at all the formulas. Really all you need is the original and the transpose, but I added the other just for clarity and error checking. HTH.
Book1
ABCDEFG
1ORIGINALMATRIXMATRIXINVERSE
22001/200
3010010
40230-2/31/3
5
6
7MATRIXTRANSPOSEFINALRESULT
8200100
901201-2/3
1000302-1/3
11
12
13FormulaIS:=MMULT(MMULT(A2:C4,MINVERSE(MMULT(A2:C4,A8:C10))),A2:C4)
Sheet1

#### ssaifan

##### Active Member
thanks

i did this but what if the size of my matrix changes....every time....i may have 200 rows or 20 ....

thats why i needed a macro...
can any1 help ?

sam

#### martinee

##### Well-known Member
Well, using the indirect function, you can make it quasi-dynamic. Give this a shot? Formula is:

=MMULT(MMULT(INDIRECT(\$F\$1),MINVERSE(MMULT(INDIRECT(\$F\$1),INDIRECT(\$F\$2)))),INDIRECT(\$F\$1))

This is entered in E8 while E8:G10 is highlighted. I confirmed with Ctrl-Shift-Enter, but not sure I had to. HTH.
Book3
ABCDEFG
1ORIGINALMATRIXMainArray\$A\$2:\$C\$4
2200InverseArray\$A\$8:\$C\$10
3010
4023
5
6
7MATRIXTRANSPOSEFINALRESULT
8200100
901201-0.66666667
1000302-0.33333333
Sheet1

Replies
5
Views
159
Replies
1
Views
268
Replies
1
Views
415
Replies
15
Views
381
Replies
0
Views
274

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,118
Messages
5,768,219
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back