I made a stereoscopic model in October last year, however, choosing the wrong colors (red and blue on a black background, the effect was very weak (if any). This series was suggested to me last week by one of the guests here on the website, Don L. (thanks Don!), who sent me his stereoscopic cube. I opened the model, put on my goggles and his cube literally popped out of the screen.

I realized that white (which I always avoided) is a a good background, which combined with a red object and another a light blue offset object all make a nice setup for stereoscopic imaging. The red can be almost anything (orange, yellow and even green).

Now I quote from Wikipedia: “Stereoscopy (also called stereoscopic or 3-D imaging) refers to a technique for creating or enhancing the illusion of depth in an image by presenting two offset images separately to the left and right eye of the viewer. Both of these 2-D offset images are then combined in the brain to give the perception of 3-D depth.

Three strategies have been used to accomplish this: have the viewer wear eyeglasses to combine separate images from two offset sources, have the viewer wear eyeglasses to filter offset images from a single source separated to each eye, or have the light source split the images into the viewer’s eyes (no glasses required).”

This part of the tutorial introduces the reader to a more efficient implementation of the composite object rotation in a 3D Cartesian space.

## Excel anaglyph stereoscopy #1 – an improved perspective rotation

– This is the first section of a tutorial on creating Excel stereoscopic models.

– Up until now we performed all the rotations separately, each rotation taking up three separate

columns. This tutorial builds a simple model where all rotations are performed in a single step.

– We know that any rotation is a linear operation and can be expressed as a matrix product

between a square “rotation” matrix and a column matrix (or vector) the “coordinate” matrix.

– The matrix product is an associative operation, therefore we can perform all three rotations in

one operation but this time the rotation matrix is the product of three matrices: the azimuth

rotation matrix, the altitude rotation matrix and the roll rotation matrix.

### Excel model implementation – the input parameters:

by George Lungu

– Open a new workbook and create a parameter area like the one

in the snapshot to the right.

– Column A contains labels only. Plug in the number you see there.

– There are a total of three buttons: the upper button is called

“Yaw”, the middle button is called “Pitch” and the lower button is

called “Roll”

– All the buttons have a range of [-1, 72] (Min=-1 and Max=72).

– The angles are expressed in degrees.

– Name the following cells: B2: “Yaw”, B4: “Pitch”, B6: “Roll”,

B8: “Eye_Screen”, B10: “Screen_Origin”

<www.excelunusual.com>

1

### The button macros:

Private Sub Yaw_Change()

If Yaw > 71 Then Yaw = 0

– These three macros are practically identical. If Yaw < 0 Then Yaw = 71

Range(“B2”) = 5 * Yaw.Value

– When assigned to a spin button this type of macro has a roll

End Sub

over property, namely while increasing the value in a cell in 5-

Private Sub Pitch_Change()

degrees increments, when reaching 355 the macro will roll

If Pitch > 71 Then Pitch = 0

If Pitch < 0 Then Pitch = 71

over to 0 rather than climb to 360.

[B4] = 5 * Pitch.Value

– Similarly when the value in the target cell reaches 0 (while

End Sub

decrementing the macro variable) the macro will roll over to and continue to go down from there rather that changing If Roll > 71 Then Roll = 0

Private Sub Roll_Change()

If Roll < 0 Then Roll = 71

the target cell value to negative numbers.

[B6] = 5 * Roll.Value

End Sub

### Creating a cube:

C 1 1 -1 0.8

– We will create a cube using a constant D 1 -1 -1

E H

A -1 -1 -1 F 0.5

data table: G

E -1 -1 1

– Empty rows in the table translate in curve

F -1 1 1 0.2

discontinuity. G 1 1 1

-1 -0.7 -0.4 -0.1 0.2 0.5 0.8

H 1 -1 1 -0.1

– This is not a displayable curve yet until we

E -1 -1 1

perform the 3D-2D perspective conversion. C

-0.4

B

B -1 1 -1

– You are not supposed to chart the data F -1 1 1

D

-0.7

yet. The chart to the left will be crated later

C 1 1 -1

A

G 1 1 1

in the presentation.

-1

D 1 -1 -1

H 1 -1 1

<www.excelunusual.com>

2

### A generic rotation matrix:

– Always we rotate around a certain axis. While the coordinate corresponding to the rotation

axis remains unchanged, the other two coordinates for any point belonging to the body change.

This makes a rotation a 2D transformation provided we choose the Cartesian coordinate system

appropriately.

– The following formulas were derived on this blog before. If we rotate a point around the z axis

for instance (yaw rotation) we have valid the following formulas.

<www.excelunusual.com>

3

final roll pitch yaw initial

first multiplication, the result

is a new coordinate vector

second multiplication, the result

is another coordinate vector

last multiplication, the result is

the final coordinate vector

The matrix multiplication is not commutative but it is associative therefore we can rewrite

the above relationship as:

Where the composite rotation matrix is:

composite roll pitch yaw

<www.excelunusual.com>

4

### Worksheet implementation of the three rotation matrices and the composite matrix:The Yaw matrix:

– Cells E4, F4, G2, G3 are all zero, G4: “=1”

– E2: “=COS(Yaw*PI()/180)”, F2: “=-SIN(Yaw*PI()/180)”

– E3: “=SIN(Yaw*PI()/180)”, F3: “=COS(Yaw*PI()/180)”

The Pitch matrix:

– Cells J2, K2, I3, I4 are all zero, I2: “=1”

– J3: “=COS(Pitch*PI()/180)”, K3: “=-SIN(Pitch*PI()/180)”

– J4: “=SIN(Pitch*PI()/180)”, K4: “=COS(Pitch*PI()/180)”

The Composite matrix:

The Roll matrix:

– I7: “=MMULT(E7:G9,MMULT(I2:K4,E2:G4))”

– Cells E4, F4, G2, G3 are all zero, G4: “=1”

after typing that in cell I7 and hitting return,

– E7: “=COS(Roll*PI()/180)”, G7: “=-SIN(Roll*PI()/180)”

select range I7:K9 and holding F2 down press

– E9: “=SIN(Roll*PI()/180)”, G9: “=COS(Roll*PI()/180)” Ctrl+Shift+Enter.

Create a table with the data for the composite rotation of the cube:

– D16: “=TRANSPOSE(MMULT($I$7:$K$9,TRANSPOSE(A16:C16)))”

after typing that in cell D16 and hitting return, select range

D16:F16 and holding down F2 press Ctrl+Shift+Enter.

– Copy range D16:F16 down to row 34 then delete the

following ranges: D26:F26, D29:F29, D32:F32.

<www.excelunusual.com>

5

### Create the 3D-2D perspective conversion formulas:

– The formulas for the 3D-2D perspective conversion were derived on

this blog before. They are shown on the upper right side of this page.

– ES is the distance between eye and screen and SO is the distance

between the screen and the origin of the object system of coordinates

– G16: “=Eye_Screen*D16/(Eye_Screen+Screen_Origin+E16)”

– H16: “=Eye_Screen*F16/(Eye_Screen+Screen_Origin+E16)” then copy G16:H16 down to G34:H34.

After this delete the following ranges: G26:H26, G29:H29, G32:H32

### Charting the cube :

– Chart range G16:H34 onto a square 2D scatter

chart having both axes scaled between -1 and +1.

– You are ready to use the model by adjusting the

angles of rotation and watching the charted cube.

– As opposed to previous models of this type this one

has the advantage of simplicity and space saving since

only 3 columns were used for rotation instead of 9.

– You don’t have to use the Excel matrix formulas, you

can do the matrix multiplication by hand following the

same guidelines and you are still at a great advantage.

– The model is computational efficient too, since a lot

of the rotation mathematics is performed in small

area (the matrices). For large objects with many

vertices this is the way to go.

to be continued…

<www.excelunusual.com>

6

Francisco, I got the file but I still don’t understand what you want me to do. There are some lines there and it is hardly anything you can do with a line in 3D. If you however have a shape and you don’t understand the tutorials just take one of my files and plug in your 3D data instead of the original data and you are ready to go. My files are fully functional, they rotate the shape and it’s stereo-3D. Cheers, George

Hi, George

I checked your website everyday. Great work. You, unlike others on utube,

sahre a a lot of knowledge.

I have learned a grest deal of knowledge by now.

I created a moving 45 degree graph by using the offset. I will email you my work.

I have a question. I wonder how can I apply the Stereoscopic Excell Rotationg Techniques to make my animated 45 degree graph rotate in 3D like the one that is presented

in utube:https://www.youtube.com/watch?v=iYcOzIRyjbk&feature=youtube_gdata_player Parameterizing a Circle – Animated spreadsheet

Francisco

furena2000@hotmail.com

Thanks for the clarification, Peter. That sounds very good!

Do you happen to have 3D glasses at home??? I am posting something very elementary today but the possibilities are quite promissing.

Just noticed I did not respond to your ‘CV speculation’. My technical background is math, some theoretical physics, astronomy, aircraft structures, FE methods, design optimisation. As a math graduate I learnt “don’t attempt to remember anything you could work out”; understanding, not memory, drives the show. More abstract concepts are part of the understanding though, particularly when they may be applied to seemingly widely different application areas.

A reasonable degree of overlap with your ideas and motivation I think.

Thanks, Don! I will check it out. The whole stereoscopy is a gross approximation to start with and if I look at 3D images and see how how far from reality they are I think a 3% aproximation could not be perceived even by a trained person. However this 3% approximation comes with a massive cut in the amount of calculations. Moreover retrofitting a file for 3D with this approach is a breeze. A file like the coaster must take minutes to upgrades especially if you put everything in a nice custom defined vba function. All the best, George

Hi George, yes the range scope does work on the same principles and I have now managed to get the email to send to you. Take a look at what I’ve done, play with the controls for eye separation and the binocular angle (the degree to which you cross-eye the direction of the scopes in the range scope analogy and you will find the medium that suits you.

I also did equate the right eye v’s to the left eye v’s and the result is still fine, so it’s reasonable to use this approximation. In fact, even though it’s an approximation, it is easier to see. and this is because it is an approximation of what you should see in your peripheral vision if you’re looking at the centre of the scene, but it’s inaccurate if you’re not looking at the centre of the scene, but the peripheral objects directly. in that case, using the same v’s for left and right eyes becomes more pleasing to view than having the vertical difference making things uncomfortable for your eyes trying to accommodate vertical mismatches. I will now send the second version of the roller coaster that uses v_right = v_left.

Cheers,

Don

Don. Thanks for the reminder of Affine transformations. I had a vague memory that perspective transformations could be handled with a single 4×4 matrix multiplication but the mysteries of the glide reflection and other delights were a long time ago.

Thanks Don! I didn’t find the file in the support inbox. I now how to do it as two simple translations in the “u” parameter of the 3D-2D conversion and last night at the gym I figured out very a very neat way of understanding and explaining it. It is essentially just like using a “scissiors telescope” from in artillery or telemetry. To enhance the perspective view much more than your eyes can do you take two images from the right and left or your head with a span between the “objectives” or between cameras or 1 or a few meters let’s say. Then, using periscope type prisms you bring the the two images together (in case of photography you mount one camera at an arm lenght to the right and another one at an arm length to the left at the shoulder height and both facing forward, or slightly inward depending on the effects you want to achieve). Therefore now, after bringing the pictures or images together at a distance between themselves equal to the eye-span, deliver them separately to each eye (right to right and left to left). Of course the brain will make you look cross eyed while watching areas in the photographs close to the imaging point and look straight while watching the horizon. This is just like real life. If you make a small game you need to set the scope-span small and in the case of a flight simulator (you are quite far from the scene) you need largely spaced collection telescopes (cameras) in order to perceive 3D. You should fix the inter eye span (offset) and adjust the telescope offset depending on the scene (like in the case of a scissior scope you can adjust the angle from 0 (a simple trench telescope) to 180 degrees for full telemetry depending on the purpose you need to achieve). There are some v-errors but they are very small (few percent). You won’t ever notice them. Actually in real movies those differences are bad and people try to get rid of them. Once you understand the scissior scope analogy the geometry and math are trivial and the model is just slightly more complex except for charting 2x the number of points.

George,

Thanks for the reference to the example that I sent you and I am also sending you one with the roller coaster implemented with affine transforms as the method for rotations and translations. Check for that on the support inbox. It was done on Excel 2007 as alas I don’t have 2003 on my home machine at the moment.

I have implemented controls for the separation between left and right eyes, the “binocular angle” to use to rotate left and right scenes by and a “depth” behind the screen where the rotation z axis is located. Play around with these to see that you change the depth of the scene and the apparent depth within the scene of the screen plane.

I am still thinking about whether there is a shortcut by making just an offset left to right instead of the rotation of the scene, but if you do that, the whole scene appears behind the screen and none of it comes forward. I’m also considering whether it works to use a common v coordinate in the two perspective views as would be valid if there was no rotation of the right eye scene compared with the left. With the rotation of the scene about an axis somewhere behind the screen plane, then points on the ground plane will have different v’s left to right anywhere off the centre vertical axis of the view.

Will continue experimentation tomorrow morning and let you know my findings.

Peter, Thanks for posting here. Don’t impose any restrictions on yourself. If it’s not 90% fun, don’t do it. If you do it, take as much time as you like, even months if you like. I sometimes get stuck myself. Are you a mechanical engineer and FEA guru? If yes, you are the person I need on this site the most.

Coordinates do mix. I have a solution and it’s in the coaster. You need two unit segments (like a letter L), one longitudinal (with the plane) and one transversal, say parallel with your vertical stab. As you fly you need to integrate the controls (by doing the copy paste operation for these 2 segments) to find the final linear and angular coordinates since the controls are “derivative” devices. After that you apply the 3 rotations and three translations backwards for the whole scene in order to find your true position(x, y, z, alpha, beta, gamma). I have it all worked out, I just need to make a series about it. How did you think I created the coaster? By using the old “flying” method while the generated “contrail” was the coaster itself. How do I ride it? By using the method you are talking about now and which I will be using soon for the RC plane and helicopter simulator. Two years back while making the coaster I encountered control mixing issues myself. That’s why I need to make tutorials, so that I know how to do it next time from the first shot.

George. I am still here but realistially I am unlikely to write anything for you on refraction before I get back from the NAFEMS Conference in Boston. Especially if your other posts keep throwing up interesting challenges!

With the flight simulator in mind in particular, I find the sequential application of rotations about co-ordinate axes something of a nightmare; especially so, in that the order they are applied changes things once the rotation angles start to get large.

What I did instead was to work out the rotation matrix corresponding to a large angle rotation Omega about an arbitary direction defined by components (direction cosines if you insist) (n1, n2, n3). The other calculation was to start with a arbitrary, numerically specifed rotation matrix and extract the axis rotations from an ‘asin’ and two ‘atan2’ operations.

As with any ‘home developed’ solution, I suspect I should now be able to find it in text books but that’s just life.