×
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!
  • Students Click Here

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

Students Click Here

Jobs

Organizing files
5

Organizing files

Organizing files

(OP)
Any suggestions on how to organize excel calculations?  I am an HVAC engineer and try to make as many as I can, but have trouble reusing them on future projects, like a template.  It seems every job, I start from scratch.  I do computer calcs to get organized and be faster, save budget and make more money, but it doesn't seem to be working.

Looking to get into VBA for better efficiency, and considering using Access to somehow connect everything togather, per project.  I know it can be done, just haven't figured it out yet.

What do others do to stay organized and efficient?

 

RE: Organizing files

The trouble is that you are probably trying to use the wrong tool for the job.

Chances are that you need something that explicitly shows you what the equations are rather than hiding them

So many engineers use products like Matlab or Mathcad, and some use their free equivalents Octave and Smath Studio.

That's not to say that you can't do it in Excel, for that matter I've seen an FEA program written in Excel.

  

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Organizing files

Quote:

Chances are that you need something that explicitly shows you what the equations are rather than hiding them

So many engineers use products like Matlab or Mathcad, and some use their free equivalents Octave and Smath Studio.

No doubt programs like Matlab or Mathcad have their advantages over spreadsheets, especially for people who don't want to write their own maths routines, but I really don't think that producing re-usable design applications quickly and efficiently is one of them.  It seems to me that that a spreadsheet is excactly the right application for doing that.

As for how to make job specific spreadsheets more re-usable, it's hard to comment without knowing more about the applications, and what it is that prevents them from being re-usable, but some general comments are:

- Document what the application does, where formulae come from etc.
- Keep input and output separate as far as possible, and format them differently so it is obvious where data entry is expected.
- If hard copy output is required for submission to others, then do it on the spreadsheet, which will force you to think about making the process obvious to others (and yourself returning 6 months later).
- Keep a revision record (on a separate sheet at the back of the workbook)
- If on-sheet calculations are getting over-complex consider doing it in VBA.
- A good starting point for getting into VBA programming is to replace complex formulae with user defined functions in VBA.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Organizing files

Spreadsheets, by themselves, are indeed amenable to re-use.  However, without a third-party package, documentation of the Excel equations is problematic.  What distinguishes a reusable item is whether you can pick it up 1 or 2 yrs from now, and be able to use it, or understand what to modify, without completely re-engineering the sheet.

What makes Mathcad or Matlab better in that regard is that you have to name everything from the get-go, so if the variable names are sufficiently lucid, the sheet is almost self-documenting.  In the case of Mathcad, since the equations are in a recognizable graphic format, re-using, or modifying, such a sheet after a long break is relatively easier.  Doing the same in Excel is obviously possible, but requires doing extra work and discipline.

Additionally, in Mathcad, units are directly incorporatible in the equations, so if your next job comes with parameters in different units, your sheet can potentially swallow the new parameters without any modification to the sheet.  This is, oddly, particularly useful in the defense procurements, because even though the Government has mandated metric units, specifications STILL come in with yds, meters, nautical miles, and km all in the same document.  Neither Matlab nor Excel natively support transparent usage of units.  

While VBA has its place, putting calculation routine in VBA has potential pitfalls:
>  VBA is interpreted, and can run slower than built-in functions or the equivalent functions coded directly in C.
>  VBA functions will require detailed documentation, since it's abstraction into called functions means that you'll have seen the algorithm less often between any visits to the VBA code, putting you further back on the understanding slope, compared to an equation you see every time you do a calculation.
>  As with Excel and Matlab, handling of units requires extra processing, either manually, or with additional code.  This also require additional documentation to ensure that when you use the function a year later, that you have the right units system in mind.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Organizing files

IRStuff - I confess to never having looked at MathCad, perhaps I should.

Matlab seems to me to have a significant learning curve and I'm not convinced that my time isn't better spent learning other stuff; quite apart from the cost of buying another software package, which is not a lot but it's a factor for a sole practitioner.  I have Octave sitting on my desktop, but I never have time to get into it.

As for units, I have the luxury of working in an entirely metric world, but I can see that automatic unit conversion could be an issue for those who aren't.


Again from the sole practitioners perspective, I think the big issue is the time required to learn a new package effectively, and maintain and update that knowledge.  It seems to me that the time would be better spent in applying some extra discipline in the original spreadsheet creation.

As for VBA, the same comments regarding time apply as for learning a new package.  I think it's worthwhile for me, but that doesn't mean it's worthwhile for everybody.  I think the speed of VBA is a non-issue these days though.  If a VBA routine is too slow on modern computers then you probably shouldn't be trying to do the analysis solely in a spreadsheet anyway (and I'd suggest that Mathcad and Matlab probably wouldn't be up to it by themselves either).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Organizing files

One other aspect of Matlab that is easy to overlook is that it is used to develop production code - for example there is a reasonable chance that the HVAC in your car runs code that was generated in Matlab. There are programs that will compile your spreadsheets into stand-alone apps, but they are not in wide use.

"especially for people who don't want to write their own maths routines,"

Dunno what that means (sounds like a sneer, intentional or not), writing user functions in any of those programs is fundamental.  

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Organizing files

Quote:

Dunno what that means (sounds like a sneer, intentional or not), writing user functions in any of those programs is fundamental.   

Not a sneer at all.  It seems to me that the main advantage of these programs over a spreadsheet is that they have better maths functionality built in, so if you don't want to write your own maths functions in Excel there may be a benefit in learning Mathcad or Matlab.

As for producing compiled code, I don't see that providing any benefit for the needs of the OP.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Organizing files

The advantage of compiled or revealed  code is that somebody can't put in and hide untestable code like "if a>1.001 and a<1.0012 then a = 7"

Which is trivially easy and opaque in excel.

 

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Organizing files

I think the OP is referring to other things besides whether you show equations or not. The question seems to relate to how you organise the whole group of spreadsheets you have.

I use Reference Manager to name, describe and store files, but in Excel you can use Properties under the File menu option where you can input keywords etc., to describe the spreadsheet. I think you can then search for the file using the contents option but that seems painfully slow. Another way is to use File/Open and click on the properties option instead of the details option. In the properties it'll list the keywords you used etc.  as a reminder of the file's contents.  

corus

RE: Organizing files

which is used more mathcad or matlab?

i used matlab a long time ago (15yrs). very easy and useful. thanks. scott.

Thanks,
Scott

RE: Organizing files

If I were in a position that called for it, I would choose Matlab over VBA.  VBA's primary raisson d'etre is Microsoft Office automation.  For a purely mathematical/technical problem space, Matlab would be the better choice.

From overall usage, Matlab is probably more often used; one reason is its ability to generate C-code.  Additionally, since its syntax closely matches C code, it can be used as a self-documented algorithm description document.

In some cases, one uses Mathcad to develop the basic algorithm, and Matlab to flesh out the algorithm and generate production code.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Organizing files

My vote goes to MathCAD.

RE: Organizing files

One suggestion I have would be to try to anticipate problems or things that people could enter. For example, if you write a spreadsheet using laminar flow calculations, when it calculates the Reynolds number put it as an "IF" statement. Then, if Re is greater than a set amount, have it output something like "Calcuations only valid for laminar flow" instead of the actual Re.

This helps prevent somebody from grabbing the "Flow Calc.xls" file and using it without knowing what the sheet is actually doing.

Without using the "If" statement, the calculation could continue and end up with a result that might even look reasonable, but be completely wrong.

I also like to include a "Notes/references" column (which I do not print). This allows notes and explainations that may not be necessary for the person who receieves the results. For example, in preparing an estmate I might list a quote# and vendor that the quote came from. In the end, the person taking my estimate and generating the sales price doesn't care where the numbers came from, so they don't need the sheet cluttered with that information. However, the information is there if we get the job and somebody needs to know why I only allotted $3 for 30 tons of steel.

-- MechEng2005

RE: Organizing files

(OP)
All excellent advice, thank you.

I am in HVAC, so I feel Matlab and MathCad are an overkill (Ill take a closer look though).

It seems I use the calcs more for organizaing than high order math.  I will do my loads in an HVAC loads software (HAPS), then transfer the cfm numbers to the excel file, and follow through with algebraic formulas and compare airflows with Air Changes per Hour, etc.

Maybe it has more to do with ADD.

Please feel free to add any personal organizing tips you do with your calcs.  Do you place the file with the specific job? (when I do this, I seem to forgat about it)  or do you make one files, and use it for all the jobs(then I feel I am getting more like a databse) therefore, I am considering Access.


 

RE: Organizing files

Usually, my stuff is grouped by project, and every major task has its own folder, with the calculation files therein.  Note that DETAILED file names are a must.  

We've been striving, to little success, to be more formal with final calculations and estimates, but the end result is marginal, since much of the required documentation is lacking, or there is no way to trace a calculation back to a specific program version, or even what the program was, other than the ones we specifically know about, Excel, Mathcad, etc.   

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Organizing files

Every spreadsheet I do is set up in the same basic format:

Worksheet 1 - standardized calculation cover sheet

I have logic in the actual spreadsheet that reports some error or other unless every data entry cell in the calculation cover sheet is non-blank.  The intent is to force the user to record what it is he's trying to calculate.


Worksheet 2 - the calculation input and output

(1) Input cells, shaded yellow, bold blue font, unprotected
(2) Output cells, unshaded, regular and / or bold, protected

Each input cell has a "COMMENT" bubble that explains what the input should be.  Output cells are often also "commented" explaining what the output means.

I usually build some error trapping in it to prevent people from entering "PIGS" instead of a numerical viscosity in cP.


Worksheet 3 - the back-up references, equations and verification; sometimes an embedded PDF of a sample hand calculation


Completed calculations are scanned to PDF and stored in the project folder.  Revisions go to a "Current" folder and those replaced go to a "Superseded" folder.

Regards,

SNORGY.

RE: Organizing files

Snorgy, you can even "print" to PDF rather than scanning a paper copy (unless you need hand-annotations that were made to the printed copy). I use this free PDF software 'CutePDF Writer' available here www.cutepdf.com/Products/CutePDF/writer.asp.

This writer is easily installed and is compatible with Excel and most other programs. Very handy and free.


 

RE: Organizing files

Thanks TomBarsh!

I think I'll download that and give it a try.

Regards,

SNORGY.

RE: Organizing files

File locations depend... If I think it will be used <10 times or so, then I'll usually do one spreadsheet with tabs for each use. If it is something that is used on each project, I will often create a "master" and any time it's editted it gets saved to the specific job file. Doesn't help much in consolidating.

If I have a sheet with references and instructions and such, as SNORGY discussed, and it is to be saved in a job specific folder, then I make the directions and references a separate file. That way the (non-changing) information isn't saved again and again and wasting memory. I would put a link in the calculation sheet leading to the references/instructions.

-- MechEng2005

RE: Organizing files

CDxx139

Yes there is a better way. Your template needs to be able to automatically load small spreadsheets from disk given only the filename under the cursor and a loading macro that responds the something like "\L" as a Macro trigger. Of course the range within that filename would be the same as the filename. This way you can build a custom solution to various problems from carefully constructed spreadsheet "modules" that interconnect when properly inserted. I've had something like this running for my structural work for over 20 years. (Yeah - it's lotus 123".

Food for thought?

RE: Organizing files

I work in several disciplines within civil/public works industry.  As such, in simplistic terms, I have two folders one is for "projects" where all the project specific stuff is located, the other is called "general engineering" where all my accumulated reference material (and numerous spreadsheets are located).  Under this GE folder, I have a folder for water, a folder for roadway, a folder for wastewater, etc.  I keep a copy of all my "template" spreadsheets in their respective GE folder and when I start a new project will go to copy whatever spreadsheets I need for that project.  Just have to be discplined about copying the spreadsheet to the respective project folder.  I also print the calcs to pdf for keeping a record copy of what was computed.  I have a lot of hidden comments and "off screen info" in my spreadsheets.   

RE: Organizing files

I like snorgy's idea of a good excel spreedsheet.

My personal  experience with MathCad was a lot of work to get it going, only to have to do it all differently on the next job.


As far as organizing a project, i've been using MS Onenote alot lately. It took a day to get the feel for its concept, but its easy to operate. In concept, it replaces a highschool 3-ring binder with subject tabs. I create a tab, and subtabs for a project, and everything i do, letters, contract, cals, drawings, goes in to the binder under the proper tab. Its easy to do because when Onenote is installed on a computer, it installs a printer driver call "print to Onenote" so that when you print a document, you select oNenote instead of a hard printer. The software also allows you to copy/past photos, pdfs, etc.. on any page in the notebook. It also converts photo or pdf images into text for quick work searchs through the entire book.
 

RE: Organizing files

In principle, a "standard" spreadsheet in Excel or Mathcad should have the same utility, i.e., if your new task requires changing the Mathcad sheet, then you would have to change the corresponding Excel sheet as well.  I would hazard a guess that the difference may well be the frequency with which you use the two programs, and that changes you make in Excel are more transparent because you use it more often.

This may be a good place to point out that Mathcad comes with an Excel add-in, that allows integration of Mathcad sheets into Excel, and Mathcad, out-of-the-box, accepts Excel components directly in the worksheet.  So it's quite easy to have hybrid Mathcad/Excel worksheets, if so desired.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Organizing files

(OP)
IRStuff and Snorgy - very useful organizing tips

LonnieP - I like your thinking, break it up into smaller pieces.

Jatgo - I am trying to use your system, my problem is I do updates in the job specific calculation, and could work on multiple projects at the same time, and therefore have many different revisions, some go in different direction.  I think I just need to sit down and creat good template to start

etbetz - OneNote sounds like a great organizing tool, I will look into it.



 

RE: Organizing files

Yeah I have that problem too.  I usually end up customzing each spreadsheet slightly for every project.  I actually create version numbers on the base sheet and when I see fit to make a template change then I'll change the base sheet and update the version number.  

This is all easier said than done, because I too struggle at times to stay disciplined about the "method"

I've used Onenote as well.  It's a neat program and I liked it for a while.  Kind of fell out of the habit of keeping up with it though.
 

RE: Organizing files

Mathcad gets my vote for ease of documentation, readability and ease of re-use.  Units also.

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