macro help with mmult and inverse

[ssaifan]

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

 

[martinee]

What happens if (X'X)inverse doesn't exist? Just a thought.

 

[ssaifan]

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

 

[martinee]

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
	A	B	C	D	E	F	G
1	ORIGINALMATRIX				MATRIXINVERSE
2	2	0	0		1/2	0	0
3	0	1	0		0	1	0
4	0	2	3		0	-2/3	1/3
5
6
7	MATRIXTRANSPOSE				FINALRESULT
8	2	0	0		1	0	0
9	0	1	2		0	1	-2/3
10	0	0	3		0	2	-1/3
11
12
13		FormulaIS:		=MMULT(MMULT(A2:C4,MINVERSE(MMULT(A2:C4,A8:C10))),A2:C4)
Sheet1

 

[ssaifan]

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, 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
	A	B	C	D	E	F	G
1	ORIGINALMATRIX				MainArray	$A$2:$C$4
2	2	0	0		InverseArray	$A$8:$C$10
3	0	1	0
4	0	2	3
5
6
7	MATRIXTRANSPOSE				FINALRESULT
8	2	0	0		1	0	0
9	0	1	2		0	1	-0.66666667
10	0	0	3		0	2	-0.33333333
Sheet1