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?
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
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
http://www.excelcalcs.com/
RE: Organizing files
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
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
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
"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
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
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 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
i used matlab a long time ago (15yrs). very easy and useful. thanks. scott.
Thanks,
Scott
RE: Organizing files
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
RE: Organizing files
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
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
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
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
This writer is easily installed and is compatible with Excel and most other programs. Very handy and free.
RE: Organizing files
I think I'll download that and give it a try.
Regards,
SNORGY.
RE: Organizing files
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
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
RE: Organizing files
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
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
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
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