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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
4
Views
581
Replies
0
Views
108
Replies
1
Views
161
Replies
14
Views
269
Replies
0
Views
201

1,181,623
Messages
5,931,015
Members
436,771
Latest member
kwicol

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.

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