macro help with mmult and inverse

ssaifan

Active Member
Joined
Jul 29, 2003
Messages
355
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ssaifan

Active Member
Joined
Jul 29, 2003
Messages
355
i need that in the calc ...
u can do a 2 step process if u want ..
but pls help...
 

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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
Joined
Jul 29, 2003
Messages
355
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
Joined
Nov 4, 2003
Messages
960
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
 

Forum statistics

Threads
1,148,053
Messages
5,744,534
Members
423,881
Latest member
Nguyen Vu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top