Please support the development of this blog by sharing it on social media. Thank you!








All the animated models in the thumbnails above are created using plain MS Excel.
All the Excel files and PDF tutorials can be downloaded from MODELS & TUTORIALS page.
All the downloads on this site are FREE and there are hundreds of them.

I need your help!
Do you want to contribute to the development of this blog?  
It's easy for you to give something back by sharing this site on social media. 
If you help me grow the traffic, I will be back developing new content every week.
Links are another good source of traffic. Thanks for linking in!



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 mainly cell formulas with minimum of VBA in order to take advantage of the ease of “programming” and the native speed of the Excel spreadsheet.

Hi, my name is George Lungu, I am an electrical engineer and this is my blog.

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 ocasionally 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 before reading from someone else or asking someone else (no matter who). One month of continuous pondering can get you farther than a year of school and a year of reading. Trust me on this one, you are far smarter than you think (if you dare to use your mind). Be slow, speed is a big spoiler. The whole traditional education system is built on speed and superficiality. Don’t listen too much to your “external teachers”. Give instead, your “inner teacher” the respect he deserves. Again, trust me on this one, you won’t be disappointed.


Why Excel?

  1. Excel is cheap and ubiquitous, everyone has it.
  2. Excel is 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…
  3. As opposed to regular programming languages, Excel is a two-dimensional programming canvass
  4. Excel has decent built in graphics (I like using 2D scatter plots)
  5. Excel is a matrix calculator and it’s wickedly fast
  6. Excel has decent GUI capabilities within the program itself
  7. 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 Excel 2003?

2007 is very slow and bugsy.  I might be translating some of the models in Excel 2010 which seems to be decent version.

And why Microsoft?

I have to admit that for scientific modeling, Excel is the best overall deal out there for the reasons I mentioned before. Though I am not against product evolution, I decry the drop in speed (mainly charting speed) in the last two versions of Excel. And by the way, while Open Office “Calc” is allright for filling taxes, it is about one order of magnitude slower than Excel for large calculations. I admire the open-source initiatives though.

Contact me at:

This article has 103 Comments

  1. I’m thinking of developing a digital counter that displays sum of a column in EXCEL as this column is undated manually in an animate way like a digital counter display. Any urgent help highly appreciated

  2. Excellent blog page. I wish to learn more excel VBA tutorials.

    I am beginner in VBA. This sitehas lots of creativity. the best so far

    keep it up

  3. I LOVE this!!!!! though I have always liked excel above all other microsoft progs it is only now that I am digging more into it and seeing more and more and more potential for my students (engineering degree level)
    Planning on doing a lot more and this is really inspiring.

  4. hallo george, many thanks. i’ll give it another try with excel. the visualization parts are done with my proprietary 3d visualizer (solidworks -> modo -> i-spot). i’ll put it online for free once it’s finished.

  5. Vielen Dank Andreas! I always recommend anything from 97′ to 2003. but 2010 or later is still good but a bit slow as chart animations are concerned. The earlier versions are about 5x faster. Do you animate? Also if animation is not that important but you have very large computations (million cell tables for instance) go with 2010 or later. For the best artistic impression, colors, etc, use 2010 or later. For ease of development, intuitive user interface, etc, use 2003 or earlier. Tschüß

  6. hello george, thanks for this fantastic webpage and your impressive work collection. I’m working on a dynamic simulation for the development and automatic optimization of smart energy grids in open office. the model is rather complex and excel failed in the early stage with countless crashes (macbook pro with bootcamp). what ms excel version would you recommend ?

  7. Happy to see this site active once again in the new format! Honestly had missed it! Looking forward for many more fantastic things you do in Excel! Thanks for restarting it.

  8. I must say you have high quality posts here.
    Your website can go viral. You need initial boost only.
    How to get it? Search for: Etorofer’s strategies

  9. I do not know if this is the place to ask this question, but I find no where else: George Lungu or someone has programmed a “slide rule” in Excel?
    Thanks in advance.
    (Google translation)

  10. Hi Jill, I am totally swamped with the building now. About the 2010 version I need to see. Excel moves in the wrong direction and with 2007 – 2010 my models are barely crawling if working at all. There is little incentive for me to do anything about it especially that last year Google penalized me and cut my visitors to a third. It has barely recovered to a half. I might consider Java or someting similar but in a new blog. Besides, most people don’t take excel seriously though for self education there is simply no better tool than the old Excel. The new one is only good for very simple models because of charting speed. I am not an Excel expert or any type of programmer. To me the platform is irrelevant but I am still fond of the ease anyone can program almost anything in excel. Thanks for the comment, George

  11. Hi
    Just discovered your site – wish I’d found it 2 years ago when I had some down-time! Was then looking for more creative ways to use excel, and getting frustrated with 2007! Thanks for re-sparking my interest.
    Love the Harmonograph sheets, and the Spring Mass Dampers. I’m a Maths/Calculus teacher, always looking to link maths to other areas. Changing parameters and using differential equations in simple but unique/interesting ways (with that all-important physics link) will add that little spark of interest I’m looking for.
    Just wondering if (when/if you start adding to your site again), you will be doing anything for 2010 or do you think you will be sticking to 2003?
    Hope the house is going well!
    Regards, Jill

  12. Bayard, it’s a tough time now since your project seems a bit dated. In the 90’s yes, such a project would be great but now you need to really understand the market before creating anything. Unfortunately I am not good with SEO. Look into any programming for cell phones and make an app. All I can say is, expand it further. This is a good first step. Don’t get discouraged.
    Good luck, George

  13. Sad to hear about your house, George. If you are looking for more traffic, I will put a link to your site from mine and add to Linked in, G+ and FB. Hopefully it will help.

  14. Hi Jagmohan,

    My house burned down and I am in a soul searching phase. I will eventually get back to work for the blog. Right now I am learning about architecture and how to use Google Sketchup since we are going to rebuild. Please give me some G+’s or FB Likes. It helps my traffic. Thanks!

    Cheers, George

  15. Multam Bogdan, Nu sint din simpla ignoranta. O sa ma interesez cum sa fac un cont. Multam pentru sugestie, George

  16. Salut George!
    Felicitari pentru ce faci! Esti de un real ajutor!
    Doar o intrebare… De ce nu esti pe G+ ? As vrea sa pot urmari blogul tau de-acolo (pe mobil etc..)
    Toate cele bune si tine-o tot asa!

    Hello George,
    First of all , congratulations for what you do! You are huge help for me and others like me. Just a question.. Why aren’t you present on G+ ? I would like to follow your posts from there.
    Best regards and keep up the good work!

  17. Hi George,

    Im currently doing on a simulator with Excel. Its about car crash ( 2 or 3 car will moving on the road, it will crash when 2 car or more bang together. The user have to drag the car icon to the road ( cells which is coloured by black colour )

    Im using .Left and .Top to make the car icon moving on the cell.

    My main problem is, how can i set the car direction. For Example, T-junction.
    A ->
    <- B
    —————-| |————————
    | |
    | X |

    1. how can i set when A is drag to this Lane, A will move to the right. When B is in this lane, it move to left and also for X which moving up.

    Thanks thanks thanks.


  18. Hi George.

    Do you know if it is possible to run a macro in excel from a txt file?
    For example, write some loops in the txt file, and call them in VB (excel)

  19. Hi George,
    Exactly, I plan to build a solar thermal collector array using a P-mirror and a selective surface reciever. I’d like to play about with some different mirror configurations and reciever shapes (ie. circular and flat). And I was hoping that your circular mirror ray tracer could be modified to achieve this?
    Many thanks,

  20. Hi Tom, my time is very limited now but I should because a lot of people are looking to build reflectors for solar power applications. Are you mostly interested in a program that maps a mirror with certain characteristics for you, so that you can physically build it?

  21. Hello George,
    I been looking at your spherical mirror tutorial. And I find it very interesting indeed. I was wondering if was possible to modify this for parabolic mirrors?
    Many thanks,

  22. Thanks Fredrik! Things go very slow in the industry indeed.
    We are dealing with the insurance right now but at least we are out of hotels and found a decent house instead.
    All the best and good luck with your research. When you tell me about a power plantI cannot refrain from thinking at the powerplant in Springfield where Homer Simpson works :-}. Cheers, George

  23. Hello George

    I want to start with giving you a great thank you! I am a system engineer in the fields of nuclear power reactor in Sweden. At my spare time am I running a small engineering firm (easier to play around with some R&D that way in this country). For a couple of years ago did i get an idea of a product to motocross and enduro. I am surprised over how long time R&D really takes from mind to the state were you actually have a prototype. Most of the time I get stuck on things that I am lesser good at and it is here were you get into the picture. I have really learned a lot just by studying your *.pdf-documentation and also the excellent nice work *.xls-files. Once again a really big thanks for my new inspiration that you have been giving me!

    Best regards and hope that the rebuild of your house is working out really well for you!!!
    / Fredrik

  24. Victor, thanks for the kind words. For now we need to rebuild, hour house in Colorado has been burned down. All the best, George

  25. Hi George,

    It is interesting to know that there are people like you on earth who are willing to inspire others. What you have done with Excel designs is a revelation of the latent ability in man.

    Meanwhile, I am into Finance Management. I do not know how you can help me either by tutorial or referals to other sites so as to gain such wonderful knowledge in Excel application.

    You are a blessing to our generation.


  26. Hi George! I’m just passing by to say thank you very much for sharing you wonderful and useful work with Excel! You’re the man!

  27. George,
    i miei complimenti per il tuo blog.
    é il blog più interessante su excel in internet.

    [George, my compliments for your blog.
    Is the most interesting blog to excel on the internet]

    Grazie, thanks.

  28. Samy, Thanks. I am am average guy with an above average interest in gadgets, but very far from being an expert in Excel. The thrust of the site is actually prototyping various ideas (Excel is very handy here). Microsoft needs programmers and corporate “yes-men”. I seriously doubt I can easily fit in that organization. All the best! George

  29. You are a genius. Microsoft shud hire people like you instead of there useless website and all that crippled help files.
    Great work man..God bless you

  30. Saleem, I am not sure how to do that. It must be easy using VBA and you can search forum for that. Sorry, I am very busy nowadays with a contract I am working on.

  31. Thanks Eddy! I am trying to get away from this Excel mark and do just applications. I don’t intend to be seen as an advanced Excel user but rather as a dedicated hobbist happening to use Excel.

  32. Kudos to you, George. I’m known as an Excel guru, but to be as good as you in Excel, that is still a loooooooooooooong way to go and probably out of my reach. I realise that I am called a guru because the majority of mankind is completely unaware of the possibilities of Excel and/or don’t bother to invest some time in it. I’m really impressed with what I see here.

  33. Hi George,

    I have a about 5000 above record in Excel
    and i want to delete duplicate record in this list….

    I use remove duplicate option in Excel 2007 but that is not correct working
    some duplicate data cant delete,

    Please tell me Macro for delete duplicate data …
    I have a macro for duplicate data, but not correct working … missing some duplicate data.

    Please reply me ASAP,
    I am waiting .. ..

  34. Thanks for the prompt response George. Yes, I do plan to post further – I am working on using Excel for FIR filter analysis.

    Best wishes,

  35. Hi Nitin,

    I checked out your site. Good stuff but keep posting! Use what you have there (spectral analysis for instance) to develop more applied examples.

    All the best, George

  36. Im using Excel 2007 and have a table of 3 colums:
    Each Column gives a colours RGB values in numbers from 1 to 255.
    I would like the 4th Column to display the color based upon the numbers in the first row, Please solve this problem.

    Thanks and regards,

  37. Khan, it absolutely is, but I’m personally not there yet. Fluid dynamics derived from basic principles will be a great goal for me this year. In excel you are still limited to the grid size and you won’t have very elaborate graphics but if you spend some time you can model your problem from basic principles and learn much more in the process than by using dedicated software. Remember, Excel is just a powerful “slide rule”, you are the computation engine. And you need almost no programming skills.

  38. I am interested in simulating water hammer / pressure surge in pipelines due to sudden closure of valves. Is it possible in excel to give dynamic behaviour?

  39. Thanks Wolf for the great feedback. I am fairly new to Excel. I’ve been practically using it since 2003. I have many plans but now I just got a new job and I am busy with that. I however built an animated “buck” switching regulator model in Excel for work. It’s 1000 times faster than the Spice in Cadence. Of course there is some drop in precision but not too much. Some of the stuff you mentioned I want to try in the future. Keep in touch! George

  40. What an inspiration – well done! I thought I had done some cool things using (and misusing) spreadsheets, but you are orders of magnitude ahead. You exhibit great creativity and technical skill.

    BTW, I have used Visicalc (Apple ][+ 1980), Lotus 1-2-3, Quattro, QuattroPro (with excursions to Multiplan and Supercalc) and finally Excel since about 1990. Used in a systematic and disciplined manner spreadsheets can be an excellent tool, never mind what critics say. My applications included chemical thermodynamics, mass & energy balances, process modelling, fluid flow, 1,2, & 3-D transient heat transfer through multiple materials in various geometries. In pre-VBA days we used self-modifying macros, which many said was a crime! For fun I’ve done a Turtlegraphics implementation, Spirograph on steroids, recursive functions, and… Probably the most complex spreadsheet I had understood and tinkered with before I saw yours is a 16 MB Planetarium (in German) – very cool.
    Inspired by what you’ve done I might consider a spreadsheet to make 3D representations of Scalextric tracks. Software already out there treats the track sections as rigid but real track flexes and the joints are not perfectly square – it needs fuzzy geometry and some 3D plotting on a 2D chart. Compared to the complexity of what you’ve done, this should be a ‘simple’ spreadsheet, suitable for an overgrown kid.
    Thanks for a brilliant blog and reading an old engineer’s rambling reminiscences.
    Best regards

  41. Hello George. I am amazed by the capabilities of Excel, through their heat transfer simulation and random number generation, and I learn from you. Simulations of theories have tails? Greetings from Cali, Colombia.
    Luis Parada E S.
    Chemical Engineer
    Ms. Industrial and Systems Engineering

  42. Hi George, Thanks a ton for such a blog where our imagination meets visualization.
    Its a great work man. salute n best of luck to you.

  43. I am really impressed with this blog / site. Thank you for the information you are providing for free. Hope you are able to keep up the good work. I have already shared it with some other engineers and posted it to other online media!

  44. Sure Iranna, I will help you as much as I can. Charting is a very interesting topic for me. There are just a few tricks that make all the difference in the world. Cheers, George

  45. Hi George, I had some inclinations towards Excel. After looking at your blog and the power you using Excel for engineering solutions is amazing. I am studying the two-stage spring-mass-damping system given by you. On a similar model for vibration analysis I am working. I shall be needing your help for plotting graphs, as I progress in my work.

  46. Wolfgang, thanks for the feedback. I advise you to take it lightly. Chech out a couple of models rather than too many, otherwise you’re going to burn out and hate it. Excel is a great resource and you need to find something that you would like to do with it. Of course you need to do it for pure fun. George

  47. Hi George, Finally I´m checking your page, is amazing!!!, I never thought that you can do this things with Excel.
    I´ll try to follow all the model that you create and learn more about this program and your tips.


    Wolfgang Wuth.

  48. George,
    You have produced amazing examples of how powerful and versatile Excel really is. I’ve used Excel for years with statistical analysis and graphing among other things. You’ve taken it to new heights. I applaud your initiative and capabilities.
    Many thanks and good luck in all you do.
    Vaughan Dewar, PMP

  49. Mark, my problem is that I miss almost any feedback from teachers. My parents were both high school teachers and I know there is a lot of good “virtual lab” work that can be done. I would appreciate if you can give me some suggestions about what would interest you or other teachers in Australia. Cold formulas are not interesting to me unless combined with some special effects (animation for instance). Point me to an audience and I can do mostly lower level, didactic stuff for not only high school but middle school too. If I make the stuff that you suggest is there a way to advertise the blog to school in Australia? Cheers, George

  50. Hi, superb work. I am a teacher in a high school and am using Excel in IT and also in maths. Just wondered if you have done anything at a lower level.


  51. Loved your work… has given me inspiration to do something….


    All the best for future endeavors….


  52. Hi Marcelo. Whenever you have time… A few phrases will do. Mainly explain what the model does and how to use it. Thanks, George

  53. Hi George,

    Unfortunately I dont have enough time to produce so detailed material right now (maybe during my vacation). By the way, I am not interested to get paid for that and I am from Brazil.

  54. Thanks for the comment! Yes, I like Excel because it allows me to stay in control and the more control the more interesting it is. The other programs are like wheelchairs, a lot of them seem created for handicapped people. Güle güle, George

  55. Sounds good. It would be fine if you would like to publish some models but you need to make 2-3 pages largely written with your photo, name and a very brief CV plus instructions on how to use the model (which must be unprotected). You can self advertise, teach (tutorials) but you won’t get paid (if that’s important to you). Are you from Italy?

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

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


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

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

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

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

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

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

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

  65. Multumesc pentru incurajari, Coco. In masura in care am timp o sa incerc sa te ajut. Trimite-mi problema. Numai bine, George

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

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

  68. hello! can u please post a tutorial about solving matrix in excel, solving systems of eqn and find the vectors

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

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

  71. 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 ( 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 , 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

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

    y’-rot1 =SIGN(C9)*COS(ATAN(B9/C9)+$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)

    z-rot1+2 =SIGN(F9)*SIN(ATAN(G9/F9)+$G$2)*SQRT(F9^2+G9^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.

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

Leave a Reply

Your email address will not be published.