INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: Mechanical engineering and Excel

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.

RE: Mechanical engineering and Excel

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"

RE: Mechanical engineering and Excel

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/

RE: Mechanical engineering and Excel

(OP)
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.

RE: Mechanical engineering and Excel

(OP)
many thanks for the link

RE: Mechanical engineering and Excel

(OP)
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.

RE: Mechanical engineering and Excel

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:

  • 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 don't know about the database situation I'm afraid.

Quote:

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

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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"

RE: Mechanical engineering and Excel

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

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

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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

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

RE: Mechanical engineering and Excel

Quote:

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/

RE: Mechanical engineering and Excel

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"

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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!

RE: Mechanical engineering and Excel

(OP)
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.

RE: Mechanical engineering and Excel

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"

RE: Mechanical engineering and Excel

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

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

RE: Mechanical engineering and Excel

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.

RE: Mechanical engineering and Excel

Quote:

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/

RE: Mechanical engineering and Excel

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

RE: Mechanical engineering and Excel

Quote:

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

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

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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.

RE: Mechanical engineering and Excel

Quote:

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/

RE: Mechanical engineering and Excel

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

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

RE: Mechanical engineering and Excel

Quote:

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/

RE: Mechanical engineering and Excel

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?

RE: Mechanical engineering and Excel

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

RE: Mechanical engineering and Excel

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"

RE: Mechanical engineering and Excel

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

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

RE: Mechanical engineering and Excel

(OP)
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

Quote:

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/

RE: Mechanical engineering and Excel

Check out SMath Studio, it's infinitely less expensive and is all the rage.

EIT
www.HowToEngineer.com

RE: Mechanical engineering and Excel

I ran across this discussion last night. Even though it's from a few months ago I thought I'd add our experience with spreadsheets in our engineering work. Most of it has been with Excel and VBA but more recently we have looked at and tried Google sheets.

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

James - how do you find Google docs compares with Excel for your applications? I have just had a quick look at their sample spreadsheet app again (I've had an account for years, but never use it), and it still seems to have a number of issues. I looked at the sample script file and found:

- 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

Doug, I use Google doc spreadsheets, reluctantly, for personal use when sharing information within the family. The fact that the document is accessible from any browser (even a tablet, iPod, etc.) is great. But I find the spreadsheets painful to set up, maintain, and use. I usually create one in Excel, upload it, then have to tweak to the formatting peculiarities of the Google docs. The results are usable and even quite useful. But I would not want to have any sort of a complex infrastructure or ecology based on this: it is too easy to have data and formulas corrupted by unwary users; formatting ranges from being a headache to a nightmare; I'd almost think the development was done in a country where they did not think in English (yes, I am English-centric) because the logic of some menus and their commands does not seem logical to my English-centric view.

RE: Mechanical engineering and Excel

I have an old spreadsheet used for calculating shear and moment, etc. on rotating shafts. Could be a useful place to start when interested in deriving additional functionality. Granted, the parentheses get a bit cumbersome at times, but hey... we're engineers. Due Diligence is just part of what we do.

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

I take great care to make my Excel worksheets "self-documenting" in that each calculation takes up 3 lines: I enter the algebraic formula, the next line presents the formula with the numerical values substituted and formatted, the final line presents the numerical result. It is tedious and painstaking to do this but after doing so for 20+ years it is second nature to me. The result is that the worksheet can be printed out and put into a set of calculations and verified by hand at any point later in time: it is identical to "hand calculations" (although much more presentable than I would ever be able to do by hand).

RE: Mechanical engineering and Excel

If you like Excel, or even if you don't, take a visit here,
http://excelunusual.com/

I hate Windowz 8!!!!

RE: Mechanical engineering and Excel

I have used Excel for many years to produce repetitive structural calculations which help speed up the design process (Steel Beam Design, Load Take Down for Masonry Walls, Brick Retaining Walls etc). They do not have to be complicated and you do not have to use VBA to start with but if you continue then you will end up using VBA as it adds a whole new dimension to the finished article. Just make sure you have an example from a book or one of your previous calculations to test them against. Also either set limits on the input of make sure you test the spreadsheet for the full range of possible entries. And make it user friendly, the next person to use it may be you and you might not remember exactly how it operates.
GrahamG

RE: Mechanical engineering and Excel

Here's a web page embedded Excel spreadsheet using Microsoft's Skydrive file sharing system.

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

On-line Excel can use JavaScript in place of VBA (as can Google Docs). I haven't tried it, but from what I have seen, it won't be easy, especially for those of us who think in VBA rather than HTML and JavaScript. For those interested the link below is the best that I have seen:

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

Whoever it was who posted Excel Unusual, thanks a bunch! I've been clicking around since I saw it and it's given me to many ideas! I also found a neat heat transfer / CFD Excel based solver in a related search a while back:

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

I much prefer Excel over commercial software mainly because:

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

Engineered9 I posted it. I also saw BFlow. I must have made a similar "heat transfer in Excel" search that that turned up the Unusual site. It is quite good. Very imaginative. I thought it might inspire some of us.

I hate Windowz 8!!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close