Array creation by referencing individual elements and using formulas

konstobo

New Member
Joined
Jan 20, 2018
Messages
8
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.

Table.JPG


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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,133
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:

1581704607575.png


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
ABCDEFGHIJ
1Original CoordinatesDegreesTransformed coordinates
2PointXYZαβγX'Y'Z'
310.5243090120-24-0.5
42101112450135-6.571077.57106816.26346
53#VALUE!#VALUE!#VALUE!
64#VALUE!#VALUE!#VALUE!
7
8Original CoordinatesDegreesTransformed coordinates
9PointXYZαβγX'Y'Z'
1010.5243090120-24-0.5
112101112450135-6.571077.57106816.26346
123
134
Sheet10
Cell Formulas
RangeFormula
H3:H6H3=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:I6I3=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:J6J3=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:J11H10=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!
 
Last edited:

konstobo

New Member
Joined
Jan 20, 2018
Messages
8
You are awesome!
There no words to thank you enough!
I will try tomorrow and let you know how it goes.
 

Forum statistics

Threads
1,136,954
Messages
5,678,754
Members
419,782
Latest member
gc75150

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