Array creation by referencing individual elements and using formulas

[konstobo]

Hi everyone,

I hope I am not violating any forum rules by posting here.

The problem that I am trying to solve has to do with creating 2d arrays (matrices) by referencing cells and using formulas upon some of those cells.
Let me explain.

I have a table in Excel (defined as an Excel table, called "Table") with the following 10 columns as shown in the picture below.

Each row represents a point whose coordinates with respect to the original coordinating system (XYZ) are on the columns X, Y, Z.

I would like to transform those coordinates to another coordinate system, called X'Y'Z', which is the result of rotating the original coordinating system by "a" with respect to the original X axis, b with respect to the original Y axis and c with respect to the original Z axis. The new coordinates will be X', Y' and Z'.

From a mathematical point of view, the transformation is straight forward.
I have to calculate the product of a 3-element vector A=[X, Y Z] (transpose) and the of the 3 transformation matrices [Ra], [Rb], [Rc]:
A*Ra*Rb*Rc
The transformation matrices Ra, Rb, Rc are 3x3 in size, with elements such as 0, 1, cos(a), cos(b), cos(c), sin(a), sin(b), sin(c).
So for every point (or row if you will) on my table, the transformation matrices should be different.

Is there a way to write this matrices in a vector format to perform that calculation in a row style format?

I have tried to type a formula which I know is wrong, hoping that it will show you what I am trying to achieve.
In that formula for example Rx should be something like:
{1, 0, 0 ; 0, cos(radians[@α]), sin(radians[@α]) ; 0, -sin(radians[@α]), cos(radians[@α])}. But I guess it doesn't really work like that in Excel.

Thanks for taking the time to read this and for your help.
I don't mind macros myself, but this spreadsheet will be used by other macro-phobic people, thus it would be great if we could avoid them.

Regards,

K

 

[Eric W]

Interesting! I definitely had to give myself a refresher course in matrix algebra to understand what's going on, then I had to figure out how to get Excel to do it.

The basic gist of your question is that you want to create a 3X3 array to use in a formula that has dynamically changing values. Let's look at your first array:

You have 5 unique values, 1, 0, cos(a), sin(a), and -sin(a). Let's assign each of these values a number, 1=1, 2=0, 3=cos(a), 4=sin(a), 5=-sin(a). Now let's create a 3X3 array using those values in the right spots:

{1, 2, 2;
2, 3, 4;
2, 5, 3}.

Keep in mind that a comma separates items on the same row, and a semicolon means a new line.
Now let's put that into the CHOOSE function:

CHOOSE({1,2,2;2,3,4;2,5,3},1,0,cos(a),sin(a),-sin(a))

CHOOSE works by looking for an index and returning a value from that position of the options. And if you give it an array instead of a single value, it calculates all the values and returns them in the same position of the output array, giving you the array you want.

You can now incorporate that into your formula thusly:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Original Coordinates			Degrees			Transformed coordinates
2	Point	X	Y	Z	α	β	γ	X'	Y'	Z'
3	1	0.5	2	4	30	90	120	-2	4	-0.5
4	2	10	11	12	45	0	135	-6.57107	7.571068	16.26346
5	3							#VALUE!	#VALUE!	#VALUE!
6	4							#VALUE!	#VALUE!	#VALUE!
7
8		Original Coordinates			Degrees			Transformed coordinates
9	Point	X	Y	Z	α	β	γ	X'	Y'	Z'
10	1	0.5	2	4	30	90	120	-2	4	-0.5
11	2	10	11	12	45	0	135	-6.57107	7.571068	16.26346
12	3
13	4
Sheet10

Cell Formulas
Range	Formula
H3:H6	H3	=MMULT(MMULT(MMULT(Table[@[X]:[Z]],CHOOSE({1,2,2;2,3,4;2,5,3},1,0,COS(RADIANS([@α])),SIN(RADIANS([@α])),-SIN(RADIANS([@α])))),CHOOSE({3,2,5;2,1,2;4,2,3},1,0,COS(RADIANS([@β])),SIN(RADIANS([@β])),-SIN(RADIANS([@β])))),CHOOSE({3,4,2;5,3,2;2,2,1},1,0,COS(RADIANS([@γ])),SIN(RADIANS([@γ])),-SIN(RADIANS([@γ]))))
I3:I6	I3	=INDEX(MMULT(MMULT(MMULT(Table[@[X]:[Z]],CHOOSE({1,2,2;2,3,4;2,5,3},1,0,COS(RADIANS([@α])),SIN(RADIANS([@α])),-SIN(RADIANS([@α])))),CHOOSE({3,2,5;2,1,2;4,2,3},1,0,COS(RADIANS([@β])),SIN(RADIANS([@β])),-SIN(RADIANS([@β])))),CHOOSE({3,4,2;5,3,2;2,2,1},1,0,COS(RADIANS([@γ])),SIN(RADIANS([@γ])),-SIN(RADIANS([@γ])))),2)
J3:J6	J3	=INDEX(MMULT(MMULT(MMULT(Table[@[X]:[Z]],CHOOSE({1,2,2;2,3,4;2,5,3},1,0,COS(RADIANS([@α])),SIN(RADIANS([@α])),-SIN(RADIANS([@α])))),CHOOSE({3,2,5;2,1,2;4,2,3},1,0,COS(RADIANS([@β])),SIN(RADIANS([@β])),-SIN(RADIANS([@β])))),CHOOSE({3,4,2;5,3,2;2,2,1},1,0,COS(RADIANS([@γ])),SIN(RADIANS([@γ])),-SIN(RADIANS([@γ])))),3)
H10:J11	H10	=MMULT(MMULT(MMULT(B10:D10,CHOOSE({1,2,2;2,3,4;2,5,3},1,0,COS(RADIANS(E10)),SIN(RADIANS(E10)),-SIN(RADIANS(E10)))),CHOOSE({3,2,5;2,1,2;4,2,3},1,0,COS(RADIANS(F10)),SIN(RADIANS(F10)),-SIN(RADIANS(F10)))),CHOOSE({3,4,2;5,3,2;2,2,1},1,0,COS(RADIANS(G10)),SIN(RADIANS(G10)),-SIN(RADIANS(G10))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

It definitely looks a bit big and unwieldy, but it works! I did it in 2 versions. The H10:J10 version, outside of a table, just requires selecting the 3 output cells, and entering the formula once. However, multi-cell formulas are not allowed in tables, so I had to enter the formula 3 times, using INDEX to get the proper result for the proper cell. Also note that I did not use TRANSPOSE like your example. I believe that the B3:D3 is already in the proper orientation.

You'll want to check the math to make sure it all works, and that I got all the right values in the arrays. But in any case, this shows a method that should work for you. Good luck!

 

[konstobo]

You are awesome!
There no words to thank you enough!
I will try tomorrow and let you know how it goes.