### 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 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*, I am an electrical engineer and this is my blog. I currently live in Colorado.

### Why bother with this blog?

- It’s science, it’s physics, it’s electronics, mechanics, games …you name it. You could also suggest future directions of development
- Engineering is coming back in force and it will rule again. There are too many parasitic occupations nowadays and that’s why the whole castle is falling apart.
- 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
- 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.
- 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!
- This blog it’s just a lure. The best and most effective teacher lives inside you.
**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?

- Excel is cheap and ubiquitous, everyone has it.
- 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…
- As opposed to regular programming languages, Excel is a two-dimensional programming canvass
- Excel has decent built in graphics (I personally prefer using 2D scatter plots)
- Excel is a matrix calculator and it’s wickedly fast (of course not as fast as C but faster than most of programs out there for large generic mathematical computations)
- 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 Excel 2003?*

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

*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 all right for filling taxes, it is about one order of magnitude slower than Excel for large calculations. I admire the open-source initiatives though.

http://www.mail-archive.com/users@openoffice.org/msg80893.html

http://it.toolbox.com/blogs/locutus/speed-comparison-between-openoffice-calc-21-and-ms-excel-xp-13608

Simply Fantastic and motivating stuff!

Congratulations!

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)

Hello:

Exists some “slide rule” emulator in excel ?

Thanks, Terry.

Hi George, my first time here in Excelunusual site, and its great and amazing to know your work in Excel and how your helping a lot of excel community & beginners/intermediate like us….its an Excelploration!!!!

CONGRATULATIONS! I’m having a LOT of fun with your blog… ;D regards from brazil

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

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

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

Joko, Sorry, I don’t.

Hy George

Have you tutorial about heat trasfer during solidification process 2D ?

Thanks. Yes, we will recover and wel’ll be rebuilding.

sorry to hear about your house. i hope u recovered now.

Thanks Nitin!

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.

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

Hi,

Why have you stopped your great articles on this site?

regards,

Jagmohan

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

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!

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.

Regards

Alvin

I am not sure Alex but you can pose the question on mrexcel forum.

I found something related though:

http://www.mrexcel.com/forum/excel-questions/83978-can-excel-run-text-file-macro.html

http://www.excelforum.com/excel-programming-vba-macros/534151-include-vba-macros-as-text-file.html

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)

Regards

Do you mean something like this?

http://www.youtube.com/watch?v=aAhvk6HE_YI

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,

Tommy

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?

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,

Tommy

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

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

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

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.

Regards

Victor

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!

Thank you Gio for your kind words! George

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.

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

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

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

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.

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.

Eddy

Yes, Dear

A B C D

256 200 150 RGB Color

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

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,

Nitin

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

Sorry that should have been

driven by similar motivation as yours.

🙂

Hi George,

This is a fantastic website, your work is fascinating!

I run an Excel site for engineers, driven by similar work as yours. See http://www.Engineers-Excel.com

Thanks again for sharing your great work.

Best wishes,

Nitin

Do you mean something like for instance (57, 82, 186), or a different format?

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,

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.

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?

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

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

Wolf

Hola Louis! Thanks for the comment.

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

Thanks Gaurang!

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.

Thanks, Chermac.

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!

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

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.

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

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.

Regards,

Wolfgang Wuth.

Thanks, Vaughan!

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

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

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.

Mark’o

Thanks, Akshay.

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

Thanks!!!

All the best for future endeavors….

Regards,

Akshay

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

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.

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

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?

Materials science, mainly metallurgy.

Sounds interesting. What subject do you teach, Marcelo?

Hi George,

Great excel work! Congratulations!

By the way, I use some excel simulation to introduce concepts in my lectures. You can find some here: http://www.youtube.com/results?search_query=m69769&aq=f. If you find interesting to share them in your blog just let me know and I can send you the files.

Marcelo.

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,

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

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 tocalculatez(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, GeorgeHi 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, 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

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

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

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!

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

Multumesc pentru incurajari, Coco. In masura in care am timp o sa incerc sa te ajut. Trimite-mi problema. Numai bine, 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

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

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

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

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!

Thanks Claudio.

Congratulations,

I think to use your excel application on my site with your reference. They are very interesting!

Best regards

Claudio

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 (http://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 http://excelunusual.com/archive/2010/09/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

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.

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

Is there anything on the market faster than Execl and which can be programmed somewhat like Excel?

Thanks,

Dan Guibord