Organizing files 5

[cdxx139]

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?

 

[GregLocock]

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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Clyde38]

Try ExcelCalcs as part of your approach.

http://www.excelcalcs.com/

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[IDS]

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/

 

[IRstuff]

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

 

[IDS]

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/

 

[GregLocock]

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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IDS]

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/

 

[GregLocock]

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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[corus]

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

 

[purdue87]

which is used more mathcad or matlab?

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

Thanks,
Scott

 

[IRstuff]

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

 

[dogtop]

My vote goes to MathCAD.

 

[MechEng2005]

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

 

[cdxx139]

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.

 

[IRstuff]

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

 

[SNORGY]

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.

 

[TomBarsh]

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

http://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.

 

[SNORGY]

Thanks TomBarsh!

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

Regards,

SNORGY.

 

[MechEng2005]

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