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

 

[zdas04]

MartinLe,
"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.

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]

"yay for metric system"

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

Need help writing a question or understanding a reply? forum1529

 

[MartinLe]

As I said, unit conversion is an issue, it just oculd be a bigger one. Most times, a unit check by hand is unavoidable when doing a spreadsheet, most times (that I encounter) it turns out to be factors of 10 in or the other direction.

 

[IDS]

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.

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/

 

[GregLocock]

When I'm doing actual real engineering (as opposed to typing numbers into the GUI for a black box) I often start with whichever of Matlab, Mathcad, or Excel I happen to have open. But it is very common to migrate to one of the other environments after a day or so as the solution, or the problem, reveals itself. For robust long lived maintainable code there is one easy answer, so when I write apps for other people to use I write in matlab. If the datasets are enormous, matlab again. If I want pretty graphs, Excel. If I am noodling around with equations and not really sure exactly what is going on, Mathcad.



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[electricpete]

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

Attached is a spreadsheet that I developed a few years ago to manage my fantasy football team

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?Season=2013&GameWeek=8&PosID=10&LeagueID=119930

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/fantas...0&Data=Last5&Show1=9&Show2=17&LeagueID=119930

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

 

[electricpete]

Attached is a spreadsheet for analysis of a rotor to determine resonant frequencies.

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

 

[electricpete]

I agree with Greg – there are many tools and it certainly helps to be conversant with several and choose the one you feel suits the problem you’re facing. If you are starting with no tools and looking for tools to pick up, my personal opinion is excel should be among the first to pick up because it is so widely used and has such a broad range of capabilities. Others may have different opinions based on their own experiences.

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

 

[electricpete]

The last thing I'll mention in terms of "powerful" - if you're willing to spned a little time, vba provides a high degree of customizeability to build your own tools. Look around the stuff at Doug Ids's site and you'll see a wealth of tools that he built using excel to do things that many people wouldn't think can be done with excel. For example units.

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

 

[electricpete]

Just to elaborate on the nature of the nested loops..

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

 

[hacksaw]

Excel is a very useful calculation tool for many problems, but you need to be careful where high numerical accuracy and number range are involved as it only carries 13-14 digits.

 

[IDS]

Excel is a very useful calculation tool for many problems, but you need to be careful where high numerical accuracy and number range are involved as it only carries 13-14 digits

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/

 

[IRstuff]

32-bit floating point pretty has that liability, regardless of which conventional program you use. However, there are special purpose programs for extended precision. Additionally, other programs, like Matlab, can use extended precision math, and double precision is actually the default math format for Matlab.


TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IDS]

32-bit floating point pretty has that liability, regardless of which conventional program you use. However, there are special purpose programs for extended precision. Additionally, other programs, like Matlab, can use extended precision math, and double precision is actually the default math format for Matlab.

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/

 

[davidbeach]

But what real world problem needs that precision? Just because the computer (or calculator if anybody else still uses those) can give lots of digits, does it mean anything? Probably not. 3.5 digits is probably enough for any real world engineering calculation. So, if the calculating software can carry 6 digits before displaying 3.5 digits, is anything more really necessary? Do you actually know anything any better than that?

 

[MikeHalloran]

Agreed, you don't need results better than 3.5 digits, and you typically don't know the inputs to any better precision.

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

 

[zdas04]

Truss calculations are a good example of why you don't need more precision. You measure the members with a tape that has about 1/16 inch of play in the hook so you have no better confidence than +/-1/8 inch. Then the software takes that input to 16 decimal places. The third decimal place was a guess. The fourth is just nonsense. If we honor our inputs, then there is rarely a reason to go beyond single precision outside of a laboratory (and not often inside of a laboratory).

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]

"But what real world problem needs that precision?"

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.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[loki3000]

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.

 

[IDS]

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.

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/