3D Dancing Pendulums
Last week I published a post on constructing an Animated Pendulum at ExcelHero.Com with a sister post here at Chandoo.org on using simple VBA to Automate Repetitive Tasks.
I received a lot of positive feedback which is appreciated.
One respondent, Stephen, asked a question about the Pendulum not being modeled as 3D.
Which isn’t correct as the pendulums are moving on, and hence represented on, a 2D plane within 3D space.
However this got me thinking!
Pseudo 3D
There was no reason I couldn’t do a Side View and Top View of the Pendulums at the same time.
This allowed a user to view the Pendulums from the 3 views simultaneously. The animations will all be in sync as they should because they are all based on the same input and a series of named ranges.
The extra work involved adding a named range for each pendulum for the z value and copying the first chart and rearranging axis for the different views.
This post won’t describe how to do this but if you are interested I direct you to the original post at Excelhero.com where the whole project is described.
Please enjoy the revised Pseudo 3D file.
Download the Pseudo 3D Pendulum 2007/10 Only.
Why Stop at Pseudo 3D ?
But why stop at pseudo 3D?
A while back I had played with a 3D transform spreadsheet that Andy Pope had made over at AJP Excel Information.
Andy had used a series of 3D transform matrices to transform 10 points on a scatter chart and rotate them around.
So I blatently stole borrowed Andy’s example and took the logic into the Named Formula that I had used for the Dancing Pendulums.
I hope you enjoy the outcome:
The Excel version is a lot faster with smoother and more fluid animation than the Animated GIF display above, especially if you are using Internet Explorer.
Construction
I will be doing a separate post on the construction of this model when I find the time.
But until then I refer you to:
A detailed explanation of the orginal Dancing Pendulums model is available at: ExcelHero.com.
Any Popes 3D Transform is at AJP Excel Information.
In the meantime, I will be happy to answer any questions you have regarding this model in the comments.
Download
I’m rather proud of how well this turned out and have to keep reminding myself that it is just a simple Excel XY Scatter Chart.
Please enjoy the 3D Dynamic Pendulums files.
3D Dynamic Pendulums Excel 2007/10 or 3D Dynamic Pendulums Excel 97/2003
I hope you enjoy this post as much as I did making it!
Update (7 July 2011)
I have added Axis Labelling and Automatic Y Axis Rotation options
 
 

Leave a Reply
NPV() function to calculate Present Value  Sumproduct function to Consolidate Revenues? 
33 Responses to “3D Dancing Pendulums”
Fantastic! I have seen many ways how Excel is being used and amazed at people’s imagination and innovations which makes me all the more humble about my knowledge of Excel. Nevertheless I have benefitted tremandously by your site and thanks a ton for that.
Excellent!
Just great! Not in 5% I will understand it, but it looks just great!
Keep on doing things like that!
Wow – that is amazing. I’m really impressed. You are of course right that it’s only a scatter chart, lol, but it took some serious skills/mathematical knowledge to come up with this. And it looks cool!
I am even more impressed with this one than I was with the original dancing pendulums! Amazing use of Excel. As before, I had no idea Excel could be used to animate something, and I still don’t quite understand how it does it. I have far to go to be “Awesome” but hopefully with your site I will get close one day!
How cool to have inspired you to “attempt” to improve on your last post… that you achieved it is amazing.
I’m a bit of a boffin and things like this capture my imagination… now to find a practical use in the finance department and baffle my collegues.
fantastic work, love it!
Excellent …
Manu«y congrats…
One thing is for sure…You’ve got one more Fan.
Keep it up.
Amit
Awesome… I am totally amaze …
Seriously Beautiful!
That’s awesome …. Amazing.
hi,
i am from india. how can i set reminder in excel via outlook task? if u have any link pl. sent me.
Chandoo.. you are genious man.. You are helping so many people in this would with your great knowledge. Please keep doing this… You are heroooooo
What to say? all words were used by dif users……. It’s amazing. I have been struggling for years just to write a simple code on my own in VBA. It’s awresome work…
@Surya
I Appreciate the comments
Believe it or not I am by no means a good VBA coder but am handy with maths
The 3D transforms are all done with Named Formula in Excel not VBA!
The animation VBA code is not difficult
[...] http://chandoo.org/wp/2011/07/06/3ddancingpendulums/ [...]
Hi this is one of those posts that can change people’s vision of what can be done in Excel. The results are breathtaking! Will tweet to my followers.
Roger
@Roger
I truly appreciate the positive feedback
[...] 3D Dancing pendulums using Excel Charts & VBA [...]
Grrrrrr8!!!!!!!!!!!!
how may i find where is Huis_Excel_Pendulum_3D.xlsm!p5tz ?
@Art
I’m not sure about a name p5tz ?
There are Names like p5Len, p5x, p5y & p5z
These are all accessible from the Name Manager
Goto The Formula, Name Manager Tab
Hui…
in the scater chart, the Series x values for series 1 is Huis_Excel_Pendulum_3D.xlsm!p1x
i cant understand where this p1x is calculated, it is not in Name Manager nor VBA module!
OK
it is not in worksheet or module but in workbook
Hey Hui,
It is amazing. It is completed beyond my imagination what excel can do.
But Can you explain more how it works?
So far I understand you use “Load_name_range” to create Name automatically. But in the Sheet(1) ‘s macro “Pendulum_Animate()”
what does mean ” Application.Names.Add “t”, i ” . I think here is the all program to start.
Or you can refer me something to study if you don’t have time.
Kind Regards,
Tao Y
@Tao Y
The line Application.Names.Add “t”, i is the main line that drives the animation
It is inside a loop For i = 0 To 2000 Step ti and so it is rewriting the value of i every loop. With every iteration of the loop it is incrementing up by the value of ti.
So the Named Formula t, is the time counter and is then used to calculate the location of each of the the pendulum. Once the location is calculated, the location is transformed in 3D by the Rotation matrices to give the impact of rotation.
The Named Formula for the original, untransformed, coordinates of Pendulum 1 are p1x, p1y and p1z.
The Named Formula after transformation for Pendulum 1 are pp1x, pp1y and pp1z. These are the plotted coordinates
when running a program with DoEvents and I click a cell, how may I fix in the program the address of this cell ? Help please!
Did you use excel plus? Something out of this world, thank you for sharing this; you are a star.
@Hendrick
No tricks, smoke or mirrors, just plain old vanilla flavored Excel just as you buy it
No addins
I’m Glad you enjoy it.
I made it and I can’t believe how well it works
I still smile every time I view it
??? ?????????
@?????
A more descriptive comment would be great!
Hey, Chandoo and Hui.
It’s awesome to see these things working so flawlessly. Awesome job. One day I would like to know as much as you guys know now. It’d be good enough xD
I’m from Brazil, and here we use this in Portuguese. At the first glance, none of the worksheets ran, so I dug a little bit and found an addendum from Hui, about the animation code.
He said that the nonenglish Excel would possibly suffer from the same error.
It comes to be that “True” and “False” were between quotes. And the solution was just to remove that, letting this as code language and not as cell result (as far as I know – not very much)
I think this could be simple for someone who knows VBA, but as newbie as I am, this was not trivial =
Thanks a lot and now I can enjoy this marvelous things you guys did here =D
[…] 3D Dancing pendulums simulated in VBA […]
Excellent! looking very nice