Mechanical engineering and Excel 7

[loki3000]

hello,

how much is excel used in a mech. eng. office (design of products, tool design, stress...)? is it generally viable to learn advanced procedures, such as VLOOKUP, INDEX/MATCH, pivot tables, vba macro-fu?
so far i've only edited an occasional BOM from autocad/ solidworks or used it to help my boss to prepare an offer for manufacturing. it involved nothing more then sum().
i also know that they used it for fatigue life calc in the college, but not me.

can you describe some examples of advanced usage (vlookup, index, vba...) that you've solved with it?

best regards,
loki

 

[AELLC]

I use Excel extensively for structural design. I used to prefer Enercalc but found it to be very buggy and limited in many aspects. With Excel, I can program in a lot more design automation.

I don't use VBA or macros, just basic stuff, including a lot of VLOOKUP, MATCH, OFFSET, INDEX, etc.

See the attached Excel file that I called CONT BEAM. It is actually BEAMANAL, which is offered free via the AISC website.

 

[zdas04]

I know a lot of ME's that use Excel for stuff that I use MathCAD and Access for. I have never gotten a spreadsheet from one of them that didn't have a bust in some parentheses somewhere. Excel's inherent lack of any debugging facility make me very reluctant to use the advanced stuff very much. I had to use a bunch of the lookup functions to program the ASTM dew point process to generate my own McKenna Chart (none of the already done ones I could find would blow up to "E" size and still look right), and it was pretty easy to learn, but that process didn't make me want to move all of my MathCAD into Excel.

David Simpson, PE
MuleShoe Engineering

"Belief" is the acceptance of an hypotheses in the absence of data.
"Prejudice" is having an opinion not supported by the preponderance of the data.
"Knowledge" is only found through the accumulation and analysis of data.
The plural of anecdote is not "data"

 

[IDS]

I'm in the structural design area, rather than mechanical, but the requirements for design tools would be similar. I use a couple of FEA programs (Strand7 and in in-house program), then for everything else I use spreadsheets (mostly Excel, but a few Lotus 123 which still do the job they were designed for). Everything else includes:

Generation of input data for FEA programs
Extraction of results from FEA programs
Analysis of less complex structures (including continuous beams and frames)
Analysis and design of structural members
Geometrical and cross section calculations
Checking FEA results
Anything requiring tables
Anything involving calculations, simple or complex

I make extensive use of VBA, mostly in the form of User Defined Functions, but some macros as well. In my opinion VBA is well worth learning, and starting with simple but useful applications you can incorporate the learning in your normal work activities.

Non-VBA things you need to know include using Match and/or Index, VLookup and HLookup, If and SumIf etc, the trig functions, the Linest function and adding trend lines to graphs, and how the Excel charts works.

I don't use pivot-tables, although I'm sure they have their uses.

Have a look at my blog (link below). For starting with VBA search the blog for VBA UDF. There are some getting started posts back in 2008.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

loki - if you are interested in using VBA to generate UDFs, this would probably be a good place to start:

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[loki3000]

yeah,

i also think you're on the right track with python.
i've used it extensively when i still used abaqus. it also has a native interface to it and it has proven very useful. although it's not so easy to connect py to excel i think (not csv). there are issues afaik.

 

[loki3000]

many thanks for the link

 

[loki3000]

also, is it true that access is being deprecated in favor of ms sql express? i understand the reasons, but i still think that sql express is not perfect for home programmers, free or not.

i also realised i was wrong on the python connectivity with excel. i misinterpreted it at first from your blog. i thoughth that you wanted to run ordinary python scripts (on their own) and make them produce xls.

 

[IDS]

I don't know about the database situation I'm afraid.

Some general comments on Excel vs. alternatives:
It is often stated/implied that spreadsheets are not a suitable tool for serious engineering applications. I strongly disagree. I have no problem with people who prefer other tools, but for me, for very many applications, a spreadsheet (sometimes in conjunction with other software) is the easiest and most efficient way to get the job done. To suggest that it shouldn't be used because it has the flexibility to give rubbish answers when used incorrectly seems to me to be ridiculous. Any software tool can give wrong answers that may not be obvious, and all software output needs independent checks (in proportion to the potential consequences of any errors).

Regarding Excel/VBA/Python my reasons for looking at using Python are:

[ul]
[li]Python has extensive numerical applications libraries (such as Numpy and Scipy) which are being actively maintained and developed. Linking to these (via Pyxll) seems to be much easier than linking to C++ or Fortran libraries, and seems to give much better performance than linking to C#.[/li]
[li]There seem to be advantages in linking to other APIs via Python rather than VBA.[/li]
[li]Python docstrings make documentation of UDFs very easy[/li]
[li]It seems likely that VBA will receive little to no development effort from Microsoft in the future.[/li]
[/ul]

On the other hand, I'm not intending to dump VBA entirely. For most applications it remains by far the easiest way to enhance the power of spreadsheets, and I would suggest that it is well worth any engineer's time to learn the language at least to the extent that they can write a simple UDF quickly.

So for me the attraction of Python is in linking to compiled numerical analysis routines, and the attraction of Excel/VBA is in providing an easy to use and intuitive user interface with numerical data, allowing both efficient analysis, and efficient development of applications for specific tasks. Using Python as an interface to Excel seems to me a strange way of doing things, in that you are using a program with a crude user interface to access a programme that already has a powerful user interface. Going the other way provides the best of both worlds, an excellent user interface, with easy access to powerful and efficient numerical analysis routines when required.

As for whether it is worth someone who knows Python also learning VBA, I don't know. I think probably it is, but it might be worth experimenting with Python + Pyxll and see if that allows you to do everything you want.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

I don't know about the database situation I'm afraid.

Some general comments on Excel vs. alternatives:
It is often stated/implied that spreadsheets are not a suitable tool for serious engineering applications. I strongly disagree. I have no problem with people who prefer other tools, but for me, for very many applications, a spreadsheet (sometimes in conjunction with other software ) is the easiest and most efficient way to get the job done.

I was going to make a similar comment. There are many ways to get a job done and often it comes down to a matter of personal preference. Some people are better at documenting their work (for review and maintenance) than others. I'll admit that Excel perhaps presents more potential for people to be sloppy by entering only numbers and formulas and get an answer without entering a single comment or variable name. On the other hand excel has plenty of tools for documenting within a spreadsheet (for example use named ranges so a cell can be given a descriptive name) and for using vba which presents a more traditional structured programming environment. Finally, people can write spagetti code in almost any language if they choose to.
I like excel for many of the reasons Doug mentioned. Another fact of life if that excel is by far the most widely used numerical program in offices and homes. So chances are you can use it at work and home and on the road without any extra investment. And chances are also that in your career people will send you some calculations and data in excel. So it is well worth the effort to become familiar with imo. That doesn't mean it should be your only tool of course.


=====================================
(2B)+(2B)' ?

 

[MikeHalloran]

I tend to use Excel for pretty simple worksheets, usually built on the fly, or adapted from an old one that was sort of similar to what I want. I try to be disciplined about maintaining columns for units, and for English-ish descriptions of what is being computed and why. I'm sure I've tried named ranges, but I got myself confused, or more confused than is normal for me.

I've never used VBA.

I've rarely used lookup tables except when I was doing my taxes with a spreadsheet, and the annual changes in the fine print drove me to a commercial tax prep program.

I most value two functions in a spreadsheet:
Sorting, obviously, and
Goal Seeking, which is just way faster than trying to turn an equation around, or iterate it manually.



Mike Halloran
Pembroke Pines, FL, USA

 

[zdas04]

Take a look in the MathCad forum. A guy there copied in an Engineering equation straight out of Excel into an attachment. Two different Engineers (both very experienced) tried to resolve all the parentheses into an equation and got very different answers. I find that to be the norm. When I use Excel for Engineering, I always used named cells, always make sure that the answer matches a MathCad example and then am still nervous. People that don't have these problems should use Excel and get on with their lives. For the rest of us mere mortals, I wouldn't recommend it.

David Simpson, PE
MuleShoe Engineering

"Belief" is the acceptance of an hypotheses in the absence of data.
"Prejudice" is having an opinion not supported by the preponderance of the data.
"Knowledge" is only found through the accumulation and analysis of data.
The plural of anecdote is not "data"

 

[IRstuff]

One of the strongest features of Mathcad, that still remains, regardless of the version number, is the ability to accept almost any dimensional units. This has the benefit of not only vetting inputs after the equations are set up, but also of performing dimensional analysis during the creation of the worksheet, to ensure that the answers are plausible. Excel, out of the box, cannot handle this, nor can it even handle a simple micro- to kilo- conversion, which Mathcad does transparently. I've got tons of Excel sheets with 10^n multipliers, and whatnot, and it would take an hour to figure out where the scale conversion came from, and whether it was correct.

Old mechanical and structural equations sometimes have goofy constants that are either empirical or conversion factors from a particular set of input units. Mathcad eliminates the need for any of the unit conversion factors whatsoever. Area in feet, force in dynes, and output in Torr would be annoying in Excel, are trivial in Mathcad.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[MikeHalloran]

I loved MathCAD when I first tried it, ages ago.
... but I couldn't remember how to drive it the next day,
and it wasn't common enough that sending a mc file to anyone would be helpful,
and if I'm remembering correctly,
it rudely and very badly crashed my home computer,
which had a numeric coprocessor, but not a genuine Intel coprocessor,
because the game store where I bought it on a Sunday night didn't have them.

Jack Crenshaw stuck with it, and wrote at length about its recurring problems. Last I heard, he mostly liked what was then the current version, but it's still not common enough to find on most any borrowed computer, as is Excel.

Yes, I've had problems with parentheses too. I mostly avoid them by calculating terms individually in rows that are hidden and clearly marked.



Mike Halloran
Pembroke Pines, FL, USA

 

[IRstuff]

Mathsoft, and now, PTC, continue the shoot-oneself-in-foot disease. There were moments when they thought to try and widen the audience for Mathcad, with viewers and lower-cost versions. PTC has recently attempted the same with the Mathcad Prime "Express" approach of locking certain functionalities and features, but allowing non-licenseholders to use the remaining functionality freely. As mentioned above, SMath Studio is a freely available Mathcad-like package. Hopefully, these will eventually gain some more traction and be as ubiquitous as Matlab is currently.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IDS]

When I use Excel for Engineering, I always used named cells, always make sure that the answer matches a MathCad example and then am still nervous.

Surely that applies to any engineering calculation, no matter how it was produced (replacing "a Mathcad example" with "an independent calculation").

If people think that calculations done in Mathcad don't need to be checked, or can be checked less carefully, then that's a real worry.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[zdas04]

I wasn't saying that at all. I can look at a MathCad equation and see that I meant (a+1)/(b+1), but typed (a+1)/b + 1. That kind of mistake jumps out at you in MathCad, but can be quite difficult to find in Excel. My process is usually to find the equation I need, verify that the assumptions work with my problem, double check the required units, type it into MathCad, confirm that the answer makes sense with simplified data and a calculator, put in project data and get an answer, type it into Excel and make sure that I get the same answer as I got in MathCad with the simple data and with the project data, finally apply the Excel equation to a range of data (which is pretty much the only reason for me to go to Excel in the first place).

David Simpson, PE
MuleShoe Engineering

"Belief" is the acceptance of an hypotheses in the absence of data.
"Prejudice" is having an opinion not supported by the preponderance of the data.
"Knowledge" is only found through the accumulation and analysis of data.
The plural of anecdote is not "data"

 

[jhardy1]

The critical point for me that confirmed that a "dimensionally-aware" approach (such as Mathcad) is preferable to Excel came a few years ago.

My then-employer had a company spreadsheet they had been using for several years that did pipe sizing calculations in accordance with a US-sourced industry code. It had been audited and validated against the source document, and was distributed to engineers as a password-locked spreadsheet, to prevent any inadvertent corruption. When used with the test problems that were distributed with the spreadsheet, it gave "exact" solutions, precisely in accordance with the worked examples. However, on a project where my team were using it, we started to get suspicious that the pipe sizes it was spitting out were just too small to actually do the job - every pipe that we checked using alternative methods and source documents was significantly bigger, and we just couldn't correlate our results.

Being a "legacy" spreadsheet (the original coder was no longer with the company, but his macros were actually very well commented), it took us quite while to work out what the issue was. It turned out that the source document on which it was based had an incorrect empirical factor when using metric units - the reference document stated that the driving head should be expressed in kPa, but the empirical factor was actually consistent for head expressed as metres of water (a factor of approximately 10 difference). The empirical error in the source document had never been noticed before because when used with "normal" pipe flow rates and heads, the error in pipe sizing was only of second-order significance, but for the flow rates and heads in our project, it resulted in an error in the pipe diameter of approximately 50%.

In this case, the spreadsheet was "correctly" coded - it was the source document (an accepted international Standard!) that had the error. Excel just does maths on numbers, and the only "units awareness" it has is in simple text labels. As soon as we coded the source document in Mathcad, it immediately threw an "incorrect units" error message, and we were able to work out where the error lay. (And yes, we notified the authors of the code, and yes, they fixed it in later editions!)

To this day, we have no idea how many pipe systems we had previously designed that were incorrectly sized with a "validated" Excel spreadsheet!!

http://julianh72.blogspot.com

 

[MartinLe]

We use spreadsheets extensivly, for basic process engineering tied into lots of other calculations. I often wonder if there's not a better way, or better software, to do this. And for ages, I mean to invest some spare time into looking into Octave or ASCEND or another free software geared towards process engineering.

The hard parts about excel are for me understanding someone elses formulas - we often have the problem that for more complex calculations, everyomne has to do them his or herself because no one nderstands someone elses spreadsheets. Unit conversion is also an issue, but less one than for som others here - yay for metric system!

 

[loki3000]

mathCAD would be then for you, i think. it has a readable way of displaying formulas, but it's not free. Alternatively you can type the actual formula in a cell (word-like) before using it.
you won't gain much with octave if you don't like that aspect of excel, i'd say.