# Array creation by referencing individual elements and using formulas

#### konstobo

##### New Member
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:

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
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
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
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
You are awesome!
There no words to thank you enough!
I will try tomorrow and let you know how it goes.

Replies
2
Views
574
Replies
0
Views
84
Replies
5
Views
305
Replies
5
Views
375
Replies
3
Views
170

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.

### Which adblocker are you using?

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

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