Five different shape alterations are presented:
1. Resizing – which can be done independently on each axis by multiplying the coordinates by a certain factor while the shape is centered in the origin.
2. Translation – which can be done independently on each axis by adding or subtracting a term to each coordinate.
3. Simple rotation – formulas were derived for rotation of a shape centered in origin by a certain angle.
4. Rotation of a shape around its own axis within the scene – the exact derivation was not given but the recipe is to translate the shape back to origin, then rotate it and then translate the shape to its final position (translation + rotation + translation).
5. Scene rotation – this is just a simple rotation but the shapes in the scene are held fixed at their own coordinates.
[sociallocker]
[/sociallocker]
Resizing, Translating and Rotating Shapes in Excel
by George Lungu
Resizing Shapes:
The distance between two points of coordinates (xA,yA) and (xB,yB) can be increased by a factor of “n” through multiplication of all its coordinates by “n”:
After resizing by a factor of “n” the distance between the points becomes:
More general, resizing can also be performed by separate factors on each axis independently as we can see in the following example:
<excelunusual.com> 2
Shape generation:
– Insert a Worksheet named: “Size_Translate_Rotate”
– Create a 2×1 rectangle centered in origin
– Display the rectangle on a 2D scatter plot with axes sized from -4 to 4
Shape resizing buttons:
– The resize factors will be contained in cells: “C20” for X-dimension and “C23” for Y-dimension using the following macros:
Private Sub Size_Y_Change() Private Sub Size_X_Change()
Range(“C23”) = Size_Y.Value / 10 Range(“C20”) = Size_X.Value / 10
End Sub End Sub
Button properties:
The names of the buttons are “Size_X” and “Size_Y”. I arbitrarily changed their colors to red and yellow respectively Min=0, Max=20 for both buttons
<excelunusual.com> 3
Shape resizing – continuation
Create new data for the resized rectangle:
– Cell B25: “=C$20*B6”
– Copy B25 down to B29
– Cell C25: “=C$23*C6”
– Copy C25 down to C29
Plot the shifted rectangle:
Use the data in the area “B25:C29” to create a plot of the resized rectangle (the green chart in the picture)
Verify the functionality:
Click two spin buttons and verify that the rectangle is indeed scaled independently on either X axis or Y axis
<excelunusual.com> 4
Moving shapes (spatial translation):
– A shape can be moved along an axis by adding a certain increment to the respective coordinate.
– For instance if an object is located at the coordinate (x0,y0) by adding Dx to xo we can move the object along the x axis by Dx (which can be either positive or negative).
– Similarly if an object is located at the coordinate (x0,y0) by adding Dy to yo we can move the object along the y axis by Dy (which can be either positive or negative).
– We can always move an object in any direction from point A to point B by changing to xo to yo accordingly
(x0+Dx,y0+Dy)
B, A, y0
Translation
(x0,y0)
(x , y ) + (x* Dx, y Dy)
0 0 0 0
Dy, Dx
(0,0) x0 x
<excelunusual.com> 5
Translation of the previously sized rectangle:
– Create a new table with the translated rectangle data:
– Cell K8: “=B25+$L$3”
– Copy K8 down to K12
– Cell L8: “=C25+$L$6”
– Copy L8 down to L12
Shape resizing buttons:
– The resize factors will be contained in cells:
-“L3” for X-dimension and
-“L6” for Y-dimension using the following macros:
Private Sub Shift_X_Change()
Range(“L3”) = Shift_X.Value / 10
End Sub
Private Sub Shift_Y_Change()
Range(“L6”) = Shift_Y.Value / 10
End Sub
Button properties:
– The names of the buttons are “Shift_X” and “Shift_Y”
– I arbitrarily changed their colors to grey
– Min=0, Max=20 for both buttons
<excelunusual.com> 6
Translation – continuation
Plot the shifted rectangle:
– Use the data in the area “K8:L12” to create a plot of the resized rectangle (the grey chart in the picture)
Verify the functionality:
– Click the two spin buttons and verify that the rectangle is indeed translated independently on either X axis or Y axis
<excelunusual.com> 7
Rotating shapes:
Let’s review the definitions of two basic trigonometric functions on a right triangle:
Let’s take a vector with its (x,y) components:
.. and let’s rotate it by angle alpha:
Before the rotation:
- vx and vy were parallel with the x and y axes respectively and could be expressed function of the axes unit vectors i, j:
Where i and j are the x and x x y unit vectors respectively
After the rotation,
- vx and vy are no longer parallel with the x and y axes respectively and can be expressed function of the axes unit vectors i, j:
<excelunusual.com> 9
We can express v as the vector sum of vx and vy
Grouping around i and j results in:
<excelunusual.com> 10
Calling vx’ and vy’ the new coordinates of the rotated point around origin we obtain the following formulas:
(where a is the rotation angle and vx and vy are the original coordinates before the rotation)
Don’t memorize but be able to derive this at any time!
Sometimes people like to put it in matrix form but I prefer the previous expression: y
There are two ways in which we can use the rotation in practice:
1. Rotate the object around it’s own center (keeping the center of the object at the same coordinate)
2. Rotate the whole scene around a “scene pole” see next page =>
<excelunusual.com> 11
Case#1: Simple Rotation:
– Create a new table with the rotated rectangle data:
– Cell K23: “=B25*COS(RADIANS(L$20))-C25*SIN(RADIANS(L$20))”
– Cell L23: “=B25*SIN(RADIANS(L$20))+C25*COS(RADIANS(L$20))”
– Copy K23 down to K27
– Copy L23 down to L27
Rotation button:
– The rotation angle “Alpha” will be contained in cell: “L20”
– The name of the button is “Rot_simple”
– Min = -1, Max = 75
– The VBA macro is shown below in blue
Private Sub Rot_simple_Change()
If Rot_simple > 71 Then Rot_simple = 0
If Rot_simple < 0 Then Rot_simple = 71
Range(“L20”) = 5 * Rot_simple.Value
End Sub
<excelunusual.com> 12
Case#1: Simple Rotation (continuation)
Plot the shifted rectangle:
– Use the data in the area “K23:L27” to create a plot of the resized rectangle (the blue chart in the picture)
Verify the functionality:
– The plotted rectangle must be a rotated version of the rectangle in the green graph
– Click the spin button and verify that the rectangle rotates smoothly around origin
<excelunusual.com> 13
Case#2: Shape rotation around its own center:
In this case the rotation is done while the shape is centered in the origin and followed by the translation to the final position. On our spread sheet the object is
rotated in origin then translated afterward but in real life (let’s say a game) the object is applied a translation to origin, then a rotation and then a translation back
to its normal position.
Note:
For this case I won’t give detailed Excel instructions.
The implementation is left to the reader. The macro is almost identical to the previous macro and the formulas for translation and simple rotation were covered previously.
<excelunusual.com> 14
Case#3: Whole frame rotation:
Here the rotation is performed after all the necessary translations while all the shapes are in the right place (if there are more than one shape). If the scene rotation needs to be done around a different pole we need to translate the whole scene to that pole, then perform the rotation after which the opposite translation will be used to bring the scene in its original place
Note:
For this case I won’t give detailed Excel instructions.
The implementation is left to the reader.
The macro is almost identical to the previous macro and the formulas for translation and simple rotation were covered before.
<excelunusual.com> 15
Conclusions:
Five different shape alterations were shown in this presentation:
1. Resizing – which can be done independently on each axis by multiplying the coordinates by a certain factor while the shape is centered in the origin.
2. Translation – which can be done independently on each axis by adding or subtracting a term to each coordinate.
3. Simple rotation – formulas were derived for rotation around the origin by an angle of a shape centered in origin.
4. Rotation of a shape around its own axis – the exact derivation was not given but the recipe is to translate the shape back to origin, then rotate it and then translate the shape to it’s final position (translation + rotation + translation)
5. Scene rotation – this is just a simple rotation but the shapes in the scene are held fixed at their own coordinates.
A MS Excel 2003 work book with all the examples can be downloaded.
The reader is advised to examine the functionality of each section of the work sheet and try to rebuild it from scratch reading as little as possible.
This presentation should be seen as a lure or a lifeline. The farther you can reach with the least reading, the more knowledge you’ll gain. Your only real teacher lives in yourself. Spend few days, don’t rush!
by George Lungu <excelunusual.com>
Hi, found your website while I was looking for places to post about some of the art I have generated using excel, I assign a Z axis set of data and do a 3D rotation using the mmult function in excel, can check out some of my “excel art” on
Thanks Maria! I am just not very good with advertising though. I hate SEO and all that stuff.
Thanks for this tutorial.
I tried these but I have some questions. I am using Excel 2007.
When I try to resize the chart size gets resized and the rectangle almost remains the same size. e.g. when I double the size, instead of rectangle getting resized visibly, the chart axis get resized. What is the fix?
regards,
Jagmohan
I think the answer is in your next tutorial – static macros. But still would like to hear from you.
Jagmohan, I am not sure I understood your question. I downloaded the file and resized the axes in 2007 and things look good, the plotted area increases while the rectangle decreases proportionally (I doubled the axes). If you still did not solve the issue, don’t hesitate to write in more detail and I will reply soon. Cheers, George
P.S. for the advanced animated models try to get a hold on an old version (2003 or older since the new ones are very slow).
George, good introduction to coordinate transformations. I have only yesterday discovered your blog and as an engineer, I’m really impressed by whot you’ve done.
For coordinate transformations, can I suggest that using Excel’s matrix functions to implement these as Affine Transformations will improve efficiency by eliminating the need to recalculate sin and cos functions repeatedly for each coordinate in rotations. An affine transformation adds an artificial ‘z’ coordinate to 2D coordinates , so x,y pair becomes x,y,1 where 1 is an artificial z coordinate, the matrix for coordinate transformation then can get the shift_x and shift_y values added to the third column of the transformation matrix. The technique is to compile a transformation matrix that does all the transformations (linear, rotations and scaling) at the same time for all (x,y,1)’s into (x’,y’,1)’s using the MMULT function. This becomes really fast in Excel.
I’d like to send you a sample spreadsheet re-working of your transformations spreadsheet for you to look at and perhaps share with your followers.
Thanks Don for your encouragement and your offer! Matter of fact I tried and it works for a few vertices then it freezes up using matrices. Take the coaster or flight simulator for instance and it will not work with matrices even only for 1/10 of that size. In a model such as the coaster or the simulator the rotations take about 10% of the computation resources. The rest is the display. And by the way if you open the flight simulator you can see that I calculate all the combinations (sin, cos, sinsin, sincos, cossin, coscos) in a single place and then I use the same values everywhere. I doubt you can add anything significant in speed but I am interested in affine transformations so I would be interested to see a fully functional flight simulator using the transformations. Based on my experience so far the matrix multiplications in Excel are pretty much useless unless you have only a few vertices to transform. Cheers, George
Based on what I’ve seen on Wikipedia there is no advantage in speed whatsoever in using the affine, at least not in Excel. I might be wrong since I just threw a quick glance. The advantage is for people who are deep into the processor (maybe the graphical card programmers) and they save time by making the computation more homogeneous (only matrix multiplication). I am willing to bet money that if you take the simulator and replace all the rotations with some affine stuff you can increase the speed noticeably. I actually believe you won’t see any speed increase (not 1%) but I might be wrong since I have had no practical exposure to this method yet. Time is short now but later I will look into it. I like the the idea of generating plants and leafs with it :-). That’s quite attractive.
Thanks for looking into this George. I will have a look at it in the context of the rollercoaster myself and let you know how it goes. I’m gad you understood what you found on the internet in relation to Affin Transformations. in my opinion the sites returned by Google all make pretty hard going of what is a very simple idea.
Thanks, Don. Please be aware that I am on quite a tight schedule and for next year I will have less freedom than I would like. I need to cover a lot, make a net with large holes, even allow some sloppy and inaccurate work to come out. Then in the second year I will start to fill the holes. Most of this stuff I don’t know, I am learning every day. The afine transformation, based on what I’ve read has applications far beyond perpective manipulations so I will definitely come back to it. I am disappointed I cannot use matrices extensively in Excel (it freezes up when I use more that a few tens or so, or I am just too dumb to make them work) but I can emulate the same thing with plain cell formulas. Please let me know if you are more successful (using the joystick + at the least 1000 matrix multiplications). You can help me a lot by posting comments, ideas, questions, benchmarking, etc. Thanks a lot, George
Hi George,
I have spent a couple of hours on the problem and have the roller coaster working on Affine Transformations which doesn’t crash on my machine. Not sure, but the problem you may have with using a matrix multiplication for coordinate transformations is probably due to duplication of the matrix formula many times in a sheet. The matrix multiplication can however be done for a large array of points all in one hit (about 1000 points at a time).
The only limitation is that there must not be more than 5460 elements in the result of a MMULT in Excel 2003 (2007 has had that limit removed and is only limited by RAM). So, in my workbook, I calculated half the track points in one array formula and half in a second formula, then half the ground plane points in one array formula and half in another. There is the use of MMULT to “compile” the individual transformations into a single affine transform matrix in the top section behind the scatter chart which make this version run on 5 Array formulas to transform the 3598 points in the scene. In the point calculations, the fake coordinate value of 1 is unaffected by the affine transformation, so I just leave off the 4th column of the coordinate transformation MMULT array function to get (x’, y’, z’) transformed points from (x,y,z,1) input points.
I have not done any benchmarking, but it appears to run just as fast on my machine. Speed is going to be dependent on just how much of the background calculation is visible as Excel wastes a bit of energy screen updating cells when visible values are changing, etc. so the fact that my affine transformation matrices are calculated on screen behind the scatter plot probably slows it down. It has also reduced in size by around 20% I think, which is a small bonus due to the fact that I’m storing fewer formulae.
This has been a bit of a treat thinking about this and nutting it out. Looking into the structure of your work, you’re doing amazing things and I have the deepest respect for your approach and your commitment to bringing it to people. If you’re hoping to inspire people to think and learn for themselves, then you’re doing is working on me and I will recommend to the other engineers that I work with to visit your site for some inspiration.
Excel is one of the most heavily used tools in my industry and our customers use it in their standard analysis methods. They demand that calculations are not done in vba and require that all calculations are laid out and visible for checking purposes. VBA is allowed, but only for incrementing a value (like a case number or time value) or for copying and pasting a range of results as values and not for moving around formulae or destroying them after a calculation. So your “minimum vba” approach is backed up in an industrial context.
I understand that you’re very busy, as am I, so I don’t expect you to follow up on this or do any fresh work on it yourself, I simply offer this as is an hope it will be useful to you at some point.
Again, thanks for the inspiration and the great new toolbox of nifty excel widgets!
Cheers,
Don
George,
Thanks for the reply via support@excelunusual.com in response to the spreadsheet I sent you. I am glad to help.
The graphics guys do use affine transforms and the reason they tend to use exclusively multiplied matrices is because graphics cards are heavily optimised to do 3×3 and 4×4 matrix operations and, it turns out, that multiplying a 4×4 is faster than multiplying a 3×3 and adding another 3×3 (in their optimised hardware at least).
Some CAD systems are built around these principles too with all the primitive shapes and objects all stored in the database as having a simple primitive definition at the origin and a set of values for the affine matrix that scales, positions and rotates it into place. This is all just pushed to the graphics card as a set of points, lines, curves, faces, etc with the affine matrix to apply. The graphics card does the geometry, shades things, culls out hidden bits of geometry, etc and paints the result in pixels.
Graphics cards are crazy fast at math and they are now making their computational power available through OpenCL which MS may do well to use if they want to get Excel really fast on machines that are well equipped. But I expect they probably never will.
I agree with your principle that for the development of a properly deep understanding of any computational challenge at the fundamental level, you need to tackle it at the grass roots. I will continue to read your site.
Personally, I tend to make spreadsheets of things that I hear about and want to develop my understanding of such as Neural Networks and Genetic Algorithms for Optimisation, Geometry problems, etc. in order to develop exactly that kind of understanding and then sometimes I go on to write programs in other languages after having the principles well understood. Your site has given me a few new ideas.
For one thing, I did build my own version of your basic perspective projections work (using affine transforms) and added a small additional transform of the geometry for the right eye view to make the left and right eye views for 3D viewing with red/blue glasses. That’s Kinda fun and easy to do. I can send you that too if it will help your planned future work on stereoscopic viewing.
Cheers,
Don
Hi Don,
Thank you very much for the file and all the support. You saved me a lot of time with this. I am simply impressed by the fact that the model works OK with a matrix structure.
I used my jogging timer after I made sure the zoom, the chart size and the speed (fixed at 1) were all the same (I had to readjust all of them to make them equal). The result with the charting on:
– Affine transformations : 2 minutes and 13 seconds
– Basic standard calculations: 2 minutes even.
With no chart visible just the start button and the index (counter) visible:
– affine: 33.7 seconds, and standard calculations: 36 seconds (2000 frames). I can see an improvement but it’s not very large.
The sad truth is that 99 percent of my models are chart limited. I will do some more testing later.
I also see a lot of value to the technique but not so much in speed reduction in Excel. I need to get back to it and study the applications. I liked the fractal-like drawings I saw on Wikipedia related to this. It sems that the animation professionals use this technique heavily but I believe that’s because at their level their hardware is probably optimized for square matrix multiplication and has “allergy” to matrix addition.
I do this for fun and I am learning these things while I write them. My principle is: don’t ask, don’t read just use your mind and have fun with it.
Best regards, George
Hello George, thanks a lot for this interesting topic. Everything works fine !
Thanks Vlad, these are not perfect, you can find errors. I appreciate if you report them to me. George
Good to have another engineer coming out as an Excel user!
There’s some great stuff here, I’ve just posted a link from my blog (similar theme, but more civil related)
George, you’re amazing!!!!!!
Ivan
Thanks, Ivan. Download some of the files… George
Thank you George for this useful information.
I have a big projrct for Construction Management. I will show it
to you and everyone as soon I develop it.
I will refer others to your website.
People do not do things for free, but you just do it out of good heart.
Francisco