## Mechanical engineering and Excel

## Mechanical engineering and Excel

(OP)

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

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

## RE: Mechanical engineering and Excel

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.

## RE: Mechanical engineering and 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"

## RE: Mechanical engineering and Excel

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/

## RE: Mechanical engineering and Excel

http://newtonexcelbach.wordpress.com/2008/02/21/he...

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

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.

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

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.

## RE: Mechanical engineering and Excel

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:

- 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#.
- There seem to be advantages in linking to other APIs via Python rather than VBA.
- Python docstrings make documentation of UDFs very easy
- It seems likely that VBA will receive little to no development effort from Microsoft in the future.

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/

## RE: Mechanical engineering and Excel

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)' ?

## RE: Mechanical engineering and Excel

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

## RE: Mechanical engineering and 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"

## RE: Mechanical engineering and Excel

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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

## RE: Mechanical engineering and Excel

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

## RE: Mechanical engineering and Excel

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

## RE: Mechanical engineering and Excel

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/

## RE: Mechanical engineering and 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"

## RE: Mechanical engineering and Excel

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

## RE: Mechanical engineering and Excel

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!

## RE: Mechanical engineering and Excel

you won't gain much with octave if you don't like that aspect of excel, i'd say.

## RE: Mechanical engineering and Excel

"Yay for metric system"???? One of the students in my last class tried to argue with me that my answer to a problem was wrong. We talked about it for a few minutes and I put his Excel solution on the projector and one of the other students said "when you divide m^2 by km, you get mm not m". Yep SI is just perfect and it allows Engineers to not pay attention to units? Right. The students (in Adelaide) gave me all kinds of grief about having to convert lbm to lbf and psi to psf for a density calculation, but I don't see how remembering to multiply times 144 or divide by 32.174 is any different than having to remember to multiply times 1000. Unit analysis gets more tidy in SI, but it doesn't even begin to go away.

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"

## RE: Mechanical engineering and Excel

Sort of. Guess what a knot is... It's a "metric," and even officially an SI unit (1852 m/hr). The conversion between weight and mass is 9.80665, not exactly a factor of 10, is it? My point is that the powers of 10 advantage is a myth in a real world. Anything interaction with the real world introduces permeability, speed of light, speed of sound, etc. Even beyond that, real world measurements are hardly ever in exact multiples of 10. I've been looking at ultrabooks, and the ASUS Zenbook is 1.3 kg is its "weight", so how are the factors of 10 helping out there?

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

Is not the Mathcad v Excel war enough for you, without introducing the metric v imperial war? :)

But really Mathcad v Excel doesn't need to be a war either. If your preferred (or required) work style is to produce detailed calculation output that can be followed through and checked in a linear sequence, with each equation reproduced in text format, then probably Mathcad is the way to go (or a Mathcad clone, or an Excel add-in that gives Mathcad like functionality).

On the other hand if you are looking for maximum flexibility, and design output consists of a summary of inputs and results, which will be checked by a totally independent calculation, then it seems to me that there is nothing that beats a spreadsheet, and in a commercial context Excel is the obvious choice.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

Cheers

Greg Locock

New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

## RE: Mechanical engineering and Excel

I think it illustrates advanced data functions vlookup, index etc. It combines data from several different sources into a single tab (one tab per position).

See the tab QBseason:

Columns A through S are player data from the following website:

http://www.fftoday.com/stats/playerstats.php?Seaso...

A vba routine automates the processs of generating the address that accesses the proper week, year, and position and puts the data in the proper place on the spreadsheet.

Columns 8-16 are my own “projections” of points by player (row) and week (column). A given cell draws data from the player average data in the same row and combines it with opponent strength of schedule data (how many points does opponent give to this position on average) for the week indicated in column header, as obtained from here:

http://www.fftoday.com/stats/fantasystats.php?o=3&...

If you examine a formula, it is actually weighted average based on player performance over intervals of season, last-5 and last 3 as well as opponent performance. The weighting factors are given in “Main”.

The player rankings in column AE and AH are from this website:

http://www.fantasypros.com/nfl/rankings/ros-wr.php

The start time in row AJ is based on lookup obtained from here:

http://msn.foxsports.com/nfl/schedule

The row for each player is color-coded (conditional formatting) to indicate owned players (grey), waiver players

The row for each player is color-coded (conditional formatting) to indicate owned players (grey), waiver players (yellow) or available players (Green). That is based on a single list that I maintain manually in tab “MyTables”. I update it only for the changes each week (which players added or dropped). The player data in MyTables survives from week to week. Each week I run macros to pull in from all the data above and update the spreadsheet. It is half-automated, half manual… takes around 30 minutes per week.

=====================================

(2B)+(2B)' ?

## RE: Mechanical engineering and Excel

Rotor is defined in tab rotorsections. Easily changeable. Graphic representation produced using vba in tab rotorfigure.

Tab main is the program controls.

Program output in tab outsheet - critical speed as bearing stifness is varied.

Selected modeshapes in tabs 4, 5, 6.

It is a transfer matrix problem which involves a lot of nested loops and easiest done in traditional structed programming environment - here done in vba. The input and output is faciliated using excel tabs.

=====================================

(2B)+(2B)' ?

## RE: Mechanical engineering and Excel

=====================================

(2B)+(2B)' ?

## RE: Mechanical engineering and Excel

=====================================

(2B)+(2B)' ?

## RE: Mechanical engineering and Excel

The Tansfer matrix approach a uses an objective which converts a trial frequency into a scalar output. The frequency is swept on large steps over specified range. If sign reversal is detected then bisection algorithm is used to determine exact zero crossing. The process it's repeated for each bearing stiffness multiplier. .

So we have bisection algorithm loop within frequency sweep loop within bearing stiffness loop

=====================================

(2B)+(2B)' ?

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

Mathcad certainly has much better built-in facilities for working with high precision numbers than Excel, but if you need that level of precision you need to be careful whatever software you are using. See for instance:

http://communities.ptc.com/thread/42613

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Mechanical engineering and Excel

Double precision is the default in Excel as well (as it is in pretty well any FEA program).

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

However, some engineering calculations lose precision in the intermediate steps just because of the way they are done. ISTR that truss calculations work that way, by working with small differences between large numbers. I'm sure there are others.

Mike Halloran

Pembroke Pines, FL, USA

## RE: Mechanical engineering and Excel

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"

## RE: Mechanical engineering and Excel

That's a very sweeping statement. Not all engineers are building houses. Our MEs at work use over 6 digits of precision/accuracy.

Do you use GPS navigation? The GPS receiver in your phone or car uses more than 3.5 digits, otherwise, you wouldn't be any closer to your destination than the nearest milepost. GPS is used for even mundane things related to building houses. Almost all modern surveyors use GPS to determine property lines down to the inch level, which requires ~9 digits of precision and accuracy. GPS is used for farming, to squeeze out extra furrows of plantings.

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Mechanical engineering and Excel

Double precision is the default in Excel as well (as it is in pretty well any FEA program).certainly not by default, at least in abaqus. there is an option to activate it though in the job menu.

## RE: Mechanical engineering and Excel

I don't use Abaqus so I didn't know that. The message remains the same though; Excel provides at least as high precision as most FEA programs.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

EIT

www.HowToEngineer.com

## RE: Mechanical engineering and Excel

I concur with the comments above that VBA can make Excel a pretty powerful tool and enables reaching into the guts of Excel. Google sheets has google script which was pretty easy to pick up.

The blog at http://xl4sim.blogspot.com/ has examples.

Have Fun!

James A. Pike

www.xl4sim.com

www.erieztechnologies.com

## RE: Mechanical engineering and Excel

- It seemed to be very slow, even with a very simple script, especially on opening, but even after that there was a noticeable delay every time I entered a new number.

- I thought it might work better off-line, but the script didn't seem to work at all if I wasn't connected to the Internet.

- Accessing files off-line seemed difficult. I expected to be able to just open a file from the Google Drive folder using a file manager, but that didn't work, and if I tried to open Chrome and work through that, it just said there was no connection. I had to connect through the Internet, and I could then access files with the connection switched off.

So far I'm not impressed, but maybe I just haven't got it set up right. I'd be interested to know how you find it, especially with regard to recalc speed and working off-line.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

I've used the VBA functionality quite a bit. One of my projects was to take a list of chemical formulas and sort them based on a drop-down selection of number of carbons in each one, etc.

The interesting thing is behind the scenes code for MatLab or MathWorks or Mathcad,etc. seems to be relatively C related. I've taken VBA functions directly into MatLab, for example, and had little issues after some inevitable tweaks for function I/O handling. Similarly, a Mathcad or MatLab function can be inserted as a VBA function fairly easily.

If you prefer the power and simplicity of something like a Mathcad or MatLab, there's a great Open Source tool called Octave. It's free, and has all the same functionality of full MatLab, with plenty of user modules for writing equations like Mathcad, etc. It's my preferred go-to package for engineering calculations and such.

In fact, here's a link:

http://www.gnu.org/software/octave/

You can find all kinds of packages to add in for additional functionality specific to your needs as well.

http://directory.fsf.org/wiki/Octave

For example, signals processing, image / video processing, civil engineering, ODE / PDE functions, linear or non-linear optimization, a GA toolkit (my favorite so far), etc. Any data imported / exported can be with any of the standard file extensions, too... *.txt, *.xls, etc.

If you need a spreadsheet tool which functions like Excel, you can also check out LibreOffice. It's Open Source and I've barely noticed a difference in the user interface, and I'm completely happy with the functionality.

Experience: accumulated knowledge over time.

Talent: the ability to use experience.

Which is more valuable?

## RE: Mechanical engineering and Excel

## RE: Mechanical engineering and Excel

http://excelunusual.com/

I hate Windowz 8!!!!

## RE: Mechanical engineering and Excel

GrahamG

## RE: Mechanical engineering and Excel

This particular calculator solves up to a 5 span beam with each span having its own section properties. Various load and boundary conditions are available. Deflection, moment, shear and stress charts are output. The calculator updates immediately after each cell change.

Unfortunately embed feature won't work with VBA but does appear to work with most other Excel functionality including the cell and array functions.

Next up - will google sheets do the same or perhaps offer more.

Have Fun!

James A. Pike

www.xl4sim.com

www.erieztechnologies.com

## RE: Mechanical engineering and Excel

http://blogs.msdn.com/b/officeapps/archive/2013/03...

Another javascript powered spreadsheet is the Python app e-Droid Cell-Pro, which looks like it has possibilities.

As far as I can see none of these do anything that could not be done more easily if Microsoft provided a web-enabled VBA, but since that is not going to happen it looks like JavaScript is a skill worth acquiring.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Mechanical engineering and Excel

BFlow seems to be really neat.

Here's my original thread:

http://www.eng-tips.com/viewthread.cfm?qid=298781

Experience: accumulated knowledge over time.

Talent: the ability to use experience.

Which is more valuable?

## RE: Mechanical engineering and Excel

1) I can tailor it to my preferences/office design policies.

2) Most commercial software is obsessed with having to input data in separate windows, which is annoyingly clunky.

## RE: Mechanical engineering and Excel

I hate Windowz 8!!!!