Welcome to the magic world of Excel animation!
– All the animated models are created using plain MS Excel and VBA.
– All the Excel files and PDF tutorials can be downloaded from BLOG page.
– All the downloads on this site are FREE and there are hundreds of them.
Hello!
Finding engineering work quite unchallenging lately I decided to start this blog in which to share cool ways of solving engineering problems or just interesting modeling of natural phenomena in MS Excel 2003.
I use cell formulas and VBA in order to take advantage of the ease of “programming” and the native speed of the Excel spreadsheet.
My name is George Lungu and I am the author of this blog. In my daily life I am a circuit designer.
Why bother with this blog?
1. It’s science, it’s physics, it’s electronics, mechanics, games …you name it. You could also suggest future directions of development
2. Engineering is coming back in force and it will rule again. Lots of people nowadays want to either manage or market that’s why we need more engineers.
3. It might be boring at times but it’s very useful stuff. I will give it a sugar coating by producing “live” models with at least some catchy animations and sound effects
4. You can understand most of it with just a high a school background. No computer language is necessary. A little visual basic (VBA) will be involved but you’ll learn that on-the-fly. We do search Wikipedia occasionally here but as a last resort.
5. If you follow this and begin to model your own ideas with your own methods you could build some real understanding of engineering. School is good for acquiring discipline and a background, but only you can teach yourself deep knowledge by personal experience. Learn by having fun!
6. This blog it’s just a lure. The best and most effective teacher lives inside you.
7. The most important aspect: no matter what you want to do, start by thinking and experimenting before reading or asking someone else (no matter who).
Trust me on this one, you are far smarter than you think (if you dare to use your mind).
One month of continuous pondering and experimentation will get you farther than a year of school.
Learn slowly, speed is a big spoiler. The traditional education system is built on speed and superficiality.
“External teachers” were taught one thing: compliance. And that’s what they will teach you.
Trust your “inner teacher” instead. You won’t be disappointed.
Why Excel?
- Excel is cheap and and you can find it everywhere.
- It’s a good environment to do complex programming without being a programmer. It gives you the feel of of a bread board. A donkey could use it…
- As opposed to regular programming languages, Excel is a two-dimensional programming canvass
- Excel has decent built in graphics (I like using 2D scatter plots)
- As a 2D matrix calculator, Excel it’s wickedly fast
- Excel has decent GUI capabilities within the program itself
- Programming in Excel gives you more understanding of physical processes than “black box” type programs such as Matlab, Simulink, Octave since in Excel you build everything from basic formulas
All right, Excel, but why versions to use?
2007 is slow and bugsy. While most of the models should work in most versions, if one is serious about speed, Excel 2003 or earlier is a better choice. In terms or charting speed the new Excel is about 10 times slower than the old Excel (see a 2008 study here). By the same study, with 1023 MB of RAM, the Excel 2007 Line charts take 15 to 22 times as long to redraw as the Excel 2003. The recalculation time is also slower in the new Excel, but only by about 30% (study).
And why Microsoft?
I have to admit that for introductory scientific modeling, Excel is a great overall choice out there. Though I am not against product evolution, I decry the drop in speed (mainly charting speed) in the last versions of Excel. And by the way, while Open Office “Calc” is alright for filling taxes, it is about one order of magnitude slower than Excel for large calculations. I admire the open-source initiatives though.
https://www.mail-archive.com/users@openoffice.org/msg80893.html
Hi George,
This is right. Fortunately, the rotation center is in the middle of the map (square).
I recognized that it is much better for my purpose to partition the area down to a finer grid instead of doing 3D interpolation. The points do not overlap after a 45 degree rotation but you can partition it to such a fine grid that the small distance to the adjacent point does not cause significant errors. In order to produce a finer xy grid, you need 2D interpolation (to find the intermediate z values for the finer x and y axes). This method does not seem to be the method of choice for applications that need rapid calculations! My grid will have 36M points instead of 90k points. But that does not matter. Excel should do the calculations even if hundreds of 36M matrices have to be summarized.
My hardware will probably have some difficulties!
Cheers,
Julian
Hi Julian, That’s an insanely large number. I wonder how you get all this data in. Also interpolation is the easiest thing to do. Let me know. Cheers, George
Hi George,
There are thousands of regular detection points on a square, which detect added material (amount). Subsequently, the source of addition and the regular detection points rotate by 45 degrees – but not the square. Nothing moves during addition and detection.
So, this is exactly what I am searching for. I have several x-y maps (area) of deposition (z) and want to have the final amount (spatial distribution for the area). The regular grid can be converted for the other directions by calculating x` and y` using trigonometry. I think that I have to move a little bit back and forth between matrices and xyz-tables for doing this.
Origin provides a possibility to do such conversions conveniently but just for 90 degree.
After conversion, I could include the x`y`z in the original xyz table (not rotated). But this would be wrong, because these are not only additional measuring points. This is an additional amount! Thus, I have to search for the z values for the results of the rotated grid at the points of the original not rotated grid in order to summarize the amount for the not rotated area.
I searched for a solution and found that I need 3D interpolation. I am not familiar with it. Thus, I may use Origin Pro as it provides a more convenient possibility using the Shepard`s method. I do not want to make promotion for Origin. Indeed, I prefer to use Excel as it is an extremely powerful tool.
Currently, I am working in NZ.
Thanks a lot for your help! I am still planning as the calculations will take some weeks. If you find errors in my master plan or have ideas for improvement, please let me know!
It is, indeed, an awesome and extremely interesting site!
Cheers,
Julian
Julian, based on what you said, let me see if I can come up with something. Unfortunately I don’t have much time to study the software you mentioned or the Shepard method but I believe I might know what you want, essentially you have a measured (digitized and sampled) map of z(x,y) for a certain angular position and you need to calculate z(x,y ) after a 45 degree rotation around a fixed center while all other parameters remain constant. Did I get it right? I will try to make you a model hopefully within a few days. Be patient, I am quite busy nowadays. Cheers, George
Hi,
I like to solve problems using Excel but I do not know how to solve my current job.
There is an overall deposition at an area (square) by multiple depositions. The individual depositions (each square same size) are known (given in large xy-tables). The spreading of the grid is regular. The problem is that the individual depositions in the different tables refer to squares that rotate around the collective center by 45 degree. There are matrix operations that solve 90 degree but not 45.
Even more difficult than the matrix rotation is the summation after rotation by 45 degree as the values at the points after rotation cannot be simply added to the values of the points without rotation. Of course, the points do not overlay after a rotation of 45 degree.
It seems to be simple but I believe that a difficult combination of rotation and gridding would be needed. Could it be solved more easily using Excel?
I would be much obliged for any hints,
Julian
Julian, It should be easy in Excel but I need to understand your problem completely. I am familiar with depositions (evaporation, sputtering, CVD). So you have a rotating grid in 45 degrees increments? You probably rotate that hundreds of time during a deposition. You go 0 degrees for 1 minute, 45 for another minute, 90 for another minute 135 for another one ….. n*45 for another one….. and then you stop after a number of steps. You also know the x-y map of deposition rate and you need the final thickness after 100 rotations for instance and 100 minutes let’s say. I am not sure if this is it. Also do you stop the deposition during the rotation? Are you in the US? George
Great site! I’m blown away with what you have done. I think the next time someone says that I know too much about excel, I’ll show them some of your work!
I was wondering if you would be interested in helping me figure out how to create a 3D finite element spreadsheet. I have been banging my head against the wall trying to figure out how to implement it for months now. I was inspired when I saw a game physics engine demo (DMM-Digital Molecular Matter by Pixelux), where a projectile was fired at a brick wall, and then the wall blew into a bunch of different pieces.
I thought it would be a neat project to undertake, because you would have to figure out collision detection, projectile motion, fracture mechanics, material properties, etc. I never even got off the ground with it though, because I couldn’t figure out how to develop the user interface, or how to implement the fracture mechanics calculations.
Want to try to figure it out, or point me in the right direction?
If not, thanks anyway, and keep up the awesome work.
P.S. My job is relatively easy as far as engineering goes, so I have to use what I’ve learned somewhere!
Daniel,
FEA is a second priority for me right now. I wanted to do it but I need to cover more basic topics before. I never studyed it myself but it is not hard. Go through all the machanical tutorials to get familiarized with the methods first. The heat tutorials are useful too. Start simple and work your way out to complex. Do a spring mass damper than a double one and having 6 of those (easy) interconnected you already can model a square element. Then you connect more elements and, bingo, you’ve got a good start. Once you feel comfortable and want to do a large model, you can migrate to C or C++. I will do FEA myself but probably around the summer. I need to do neural networks, some more electronics, start electromagnetic fields and do a few more games until then. Cheers, George
Hi George,
Congratulations for your work.It’s amazing what you have done with excel. I thought that many of simulation presented by you were possible only with help of dedicated softwares(Wolfram Mathematica and so on)
May I send you by email a tehnical problem that I’ve to face it ?
Cheers,
coco
Multumesc pentru incurajari, Coco. In masura in care am timp o sa incerc sa te ajut. Trimite-mi problema. Numai bine, George
Coco, all those programs compared to plain Excel are like Guitar Hero compared to a real guitar. Black boxes… It makes one mentally lazy… Cheers, George
hello! can u please post a tutorial about solving matrix in excel, solving systems of eqn and find the vectors
Menezes, Can you be more explicit? You mean system of linear equations? Which vectors are you talking about, I think it’s a little ambiguous? If you formulate the problem properly I might be able to help. Cheers, George
I don’t know how to say… It is really amazing and awesome. I never thought EXCEL can do this before finding your blog.
I believe I will learn a lot from you.
Besides, thanks for your sharing with us. This knowledge is really valuable!
You are welcome Chang. The old Excel is a dumb tool. That’s what I like about it and because of this (unlike with other “smart” tools) you can enjoy, learn and build a lot with it. Smart tools on the other hand make you “less smart and creative” to use a mild term. Thanks, George
Congratulations,
I think to use your excel application on my site with your reference. They are very interesting!
Best regards
Claudio
Thanks Claudio.
I am intrigued by your 2D-3D spreadsheet PERSPECTIVE. I have attemped to add additional points to your cube.
My first step was to attempt to add the x,y,z axes to your list of points but the formulas blew up whenever I used a zero value for x, y, or z. The XL ATAN(b/a) function does not like a zero denominator; so I tried using the ATAN2(a,b) function which can handle a zero for a but not a zero for a and b. I had to devise a test for a = b = 0 before applying the ATAN2 function. Also the SQRT function does not a zero argument.
I have include your formulas and my replacement formulas (highlighted) below:
x’-rot1 =SIGN(C9)*SIN(ATAN(B9/C9)+$G$1)*SQRT(C9^2+B9^2)
=IF(AND(B9=0,C9=0),0,SIN(ATAN2(C9,B9)+$G$1))*SQRT(C9^2+B9^2)
y’-rot1 =SIGN(C9)*COS(ATAN(B9/C9)+$G$1)*SQRT(C9^2+B9^2)
=IF(AND(B9=0,C9=0),0,COS(ATAN2(C9,B9)+$G$1))*SQRT(C9^2+B9^2)
z’-rot1 =D9
x-rot1+2 =E9
y-rot1+2 =SIGN(F9)*COS(ATAN(G9/F9)+$G$2)*SQRT(F9^2+G9^2)
=IF(AND(F9=0,G9=0),0,COS(ATAN2(F9,G9)+$G$2))*SQRT(G9^2+F9^2)
z-rot1+2 =SIGN(F9)*SIN(ATAN(G9/F9)+$G$2)*SQRT(F9^2+G9^2)
=IF(AND(F9=0,G9=0),0,SIN(ATAN2(F9,G9)+$G$2))*SQRT(G9^2+F9^2)
x’ =$G$3*H9/($G$3+$G$4+I9)
y’ =$G$3*J9/($G$3+$G$4+I9)
I used the AND function to test a and b because a nested IF got too messy and the AND function clearly communicates what conditions we are testing. Also it is more easily expanded than additional IF nesting.
I was also interested in knowing the rotational angles in degrees, so I modified your spinner controls as follows:
Private Sub SpinButton1_Change()
Range(“G1”) = -SpinButton1.Value * Application.WorksheetFunction.Pi() / 45
End Sub
Private Sub SpinButton2_Change()
Range(“G2”) = -SpinButton2.Value * Application.WorksheetFunction.Pi() / 45
End Sub
and changed the MAX and Min values to 45 and -45 respectively. This provides a nice 4 degree step over -180 to +180.
Thanks for the comment, Peter. This helps my ranking since the search engines cannot read pdf’s. Check out the pyramid as I labeled the points. There is a remark on page #5 of the first 3D tutorial pdf (https://excelunusual.com/wp-content/uploads/2011/02/Perspective_1_Tutorial.pdf) about the eye to object distance becoming negative and generating artifacts. You can verify that by adjusting the screen to origin distance. As we speak I am working on the second part of the 3D presentation (talking about rotations) which will be finished by the end of the day. This time I will not use nested trigonometric functions which are a source of trouble. Again the old file https://excelunusual.com/basic-3d-2d-perspective-visualization/ , was correct but it wasn’t done right (patching with the “sign()” is a bad practice). Now I never use nested trig. functions, insted I make extensive use of triangle similarity. Cheers, George
Is there anything on the market faster than Execl and which can be programmed somewhat like Excel?
Thanks,
Dan Guibord
Daniel, The earlier versions are somewhat faster (from the 90’s). The worst is 2007 (about 10 times slower I believe). The style of programming is very important for speed in Excel. If you do FT for instance, calculate 100 frequency points over 10000 time points and use VBA to load another 100 for each step of the loop. The FT I posted is a bad example since it calculates only one point at a time. George