Call a matrix/array with an argument in MMULT?

[jxb]

To all,

I think I already know the answer but I need to ask just on case

If one defines a matrix (3x3) (CTRL+SHIFT+ENTER) is there a way of calling this matrix with an argument?

See attached for example

The only way I can think of is to create a simple function which takes theta as an argument and return a "matrix" so that I could have in any cell (using CTRL+SHIFT+ENTER)

=MMULT(myrotationmatrix(theta),A1:A3)

where A1:A3 is the input vector

Thanks
Regards
JXB

 

[RickXL]

Hi,

You can use a Named Range.

I named A1:C3 - Theta and E1:E3 - Vector.
(Highlight the cells then type the name in the top left box.)

Excel 2013
	A	B	C	D	E	F	G
1	1	2	3		1		14
2	2	2	2		2		12
3	3	2	1		3		10
Sheet1

Cell Formulas
Range	Formula
G1:G3	{=MMULT(Theta,Vector)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Theta	=Sheet1!$A$1:$C$3
Vector	=Sheet1!$E$1:$E$3

 

[jxb]

Thanks for the suggestion but the matrix A1:C3 needs to take theta (T) as a variable. A1:C£ is a defined a follows
A B C
1 0 0
0 cT sT
0 -sT cT

where cT = cos(theta) & sT = sin(Theta)

 

[RickXL]

OK,

I think a Used-Defined Function (UDF) may be the only solution. In which case why not create a UDF that will perform the rotation for you. For example:

=RotX(Theta,Vector)

(Theta and Vector are named ranges here but they need not be.)

Option Base 1
Function RotX(Theta As Double, Vector As Variant) As Variant

    Dim arr(1 To 3, 1 To 3)
    Dim var As Variant
    
    arr(1, 1) = 1: arr(1, 2) = 0: arr(1, 3) = 0
    arr(2, 1) = 0: arr(2, 2) = Cos(Theta): arr(2, 3) = Sin(Theta)
    arr(3, 1) = 0: arr(3, 2) = -Sin(Theta): arr(3, 3) = Cos(Theta)
    
    RotX = Application.WorksheetFunction.MMult(arr, Vector)

End Function

Sub test()
    Dim mat As Variant
    Dim arr(1 To 3, 1 To 1)
    arr(1, 1) = 1: arr(2, 1) = 2: arr(3, 1) = 3
    mat = RotX(1, arr)
End Sub

I have included a test macro as well but it can be run from the worksheet in the usual way. Use Ctrl + Shift + Enter to enter it:

(It looks as if you can stack them up as well.)

Excel 2013
	A	B	C	D	E
1	Theta	Vector:	Result
2	3	1	1		1
3		2	-1.55662		2
4		3	-3.25222		3
5
6	3	1	1
7		2	-1.55662
8		3	-3.25222
Sheet1

Cell Formulas
Range	Formula
C2:C4	{=rotx(Theta,Vector)}
C6:C8	{=rotx(A6,B6:B8)}
E2:E4	{=rotx(-1,rotx(1,Vector))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Theta	=Sheet1!$A$2
Vector	=Sheet1!$B$2:$B$4

 

[jxb]

Thanks for that RickXL. Did some work in Mathcad as it's easier but your UDF works.
I think one needs/may want to review is the use of cos() and sin() because in the UDF as the argument i.e. theta must in be radians

Thanks a lot

 

[RickXL]

Hi,

There is the RADIANS() function that will convert degrees to radians for you. For example:

=RADIANS(180)

So if you want to work in degrees you could either convert degrees to radians using that:

=rotx(Radians(Theta),Vector)

Or, you could add the conversion step in the UDF.

Option Base 1
Function RotX(Theta As Double, Vector As Variant) As Variant

    Dim arr(1 To 3, 1 To 3)
    Dim var As Variant
    Dim radians As Double
    
    radians = Theta * 0.0174532925
    
    arr(1, 1) = 1: arr(1, 2) = 0: arr(1, 3) = 0
    arr(2, 1) = 0: arr(2, 2) = Cos(radians): arr(2, 3) = Sin(radians)
    arr(3, 1) = 0: arr(3, 2) = -Sin(radians): arr(3, 3) = Cos(radians)
    
    RotX = Application.WorksheetFunction.MMult(arr, Vector)

End Function

 

[jxb]

that's what I did!

 

[RickXL]

that's what I did!

There is a saying about great minds