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

 

[RFreund]

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

EIT

http://www.HowToEngineer.com

 

[boyze]

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

http://www.xl4sim.com

http://www.erieztechnologies.com

 

[IDS]

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/

 

[TomBarsh]

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.

 

[Enginerd9]

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?

 

[TomBarsh]

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

 

[BigInch]

If you like Excel, or even if you don't, take a visit here,

http://excelunusual.com/

I hate Windowz 8!!!!

 

[GrahamG]

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

 

[boyze]

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

http://www.xl4sim.com

http://www.erieztechnologies.com

 

[IDS]

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/...javascript-a-vba-developer-s-perspective.aspx

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/

 

[Enginerd9]

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?

 

[AELLC]

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.

 

[BigInch]

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