Hello!
Finding engineering work quite un-challenging 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. I curently 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
Contact me at:
support@excelunusual.com
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
Great and useful information, thanks for posting!
[...] recently came across a fascinating website: ExcelUnusal.com. Its author, George Lungu writes a blog packed with incredibly useful engineering models and tutorials using Microsoft [...]
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 (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
Congratulations,
I think to use your excel application on my site with your reference. They are very interesting!
Best regards
Claudio
Thanks Claudio.
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
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
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
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,
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
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 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,
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.
Sounds interesting. What subject do you teach, Marcelo?
Materials science, mainly metallurgy.
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?
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.
Hi Marcelo. Whenever you have time… A few phrases will do. Mainly explain what the model does and how to use it. Thanks, George
Great site… Didn’t yet examine all the content..
Greetings from another excel-engineer.. we do conceptual and preliminary design by excel; also i use pikaia for optimization.
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
“There are too many parasitic occupations nowadays and that’s why the whole castle is falling apart.”
Damn right mate!!!
Thanks for commenting on my blog!
You really have taken Excel to another level!
Also i personally felt strongly we need more engineers and/or scientist and not merchant bankers. What the hack is merchant banker anyway?
I have linked this site on my blog, if you don’t mind (no you don’t need to do the same with mine!), i hope more people come to this site and gain your knowledge.
Best of luck, keep up the best work and keep in touch!!
Loved your work… has given me inspiration to do something….
Thanks!!!
All the best for future endeavors….
Regards,
Akshay
Thanks, Akshay.
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
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
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
Thanks, Vaughan!
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.
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, 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.
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
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!
Thanks, Chermac.
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 Gaurang!
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
Hola Louis! Thanks for the comment.
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
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
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?
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.
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,
Do you mean something like for instance (57, 82, 186), or a different format?
Yes, Dear
A B C D
256 200 150 RGB Color
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
Sorry that should have been
driven by similar motivation as yours.
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
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 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 .. ..