Resizing, Translating and Rotating Shapes in Excel

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 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.

[sociallocker][/sociallocker]

This article has 19 Comments

  1. 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

  2. Thanks Maria! I am just not very good with advertising though. I hate SEO and all that stuff.

  3. 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).

  4. I think the answer is in your next tutorial – static macros. But still would like to hear from you.

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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.

  11. 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.

  12. 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

  13. 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.

  14. Thanks Vlad, these are not perfect, you can find errors. I appreciate if you report them to me. George

  15. Hello George, thanks a lot for this interesting topic. Everything works fine !

  16. 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)

  17. Thanks, Ivan. Download some of the files… George

  18. 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

Leave a Reply

Your email address will not be published. Required fields are marked *