Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations GregLocock on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sluggish workbook 1

Status
Not open for further replies.

AELLC

Structural
Mar 4, 2011
1,339
What I have is a fairly complex design workbook, about 3 MB in size in the Excel 2007 .xlsx format.

Depending on inputs, numerous cells return error values such as REF!, #N/A, or #VALUE!

However this is not a problem for the "bottom line" because the output cells are programmed to display various "warning" messages or blanks i.e. cell = " "

BTW the workbook contains no macros, VBA, UDF etc. I imagine it would work better with more efficient programming, but that is not an option as far as my capabilities.

Over the years, this workbook is growing increasingly complex - the problem is it has become slow to open and run on my current computer, which is not the latest but isn't ancient, either. I am reluctant to buy a new computer because of the usual unforeseen headaches. This machine has proven to be the most durable, reliable and it is fairly fast for other tasks.

My question - if I take the time to re-program to eliminate all error values, will the workbook process noticeably quicker?
 
Replies continue below

Recommended for you

3MB file? doubtful, unless substantial portions, i.e., more than 20% is superfluous...

Macros may not necessarily improve speed either. The performance cost of function call overhead has to be traded against any potential gains om the code

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize
 
3mb, you must have lots of formulas... One way is to speed it up is to set manual recalculation option. Big button/Excel Options/Formulas/Workbook Calculations - Manual.
 
It's hard to tell if it would speed anything up.

I've had Excel get a corrupted cell that caused problems (slowed down opening & wouldn't run some macro's correctly). The way I fixed it was to copy & pasted everything into a new spreadsheet and it worked a lot better.

One thing I would suggest is to find out what the incorrect Refrences (REF!) are and eliminate them. If your spreadsheet is looking for another one that doesn't exist, it will be taking the time to look for the non-existing referenced spreadsheet.
 
zelgar,

The REF! errors are ocurring in cells containing LOOKUP - type formulas, not cells with invalid dependent links.

Depending on the inputs, those cells are returning REF! but that is not a detriment to the spreadsheet results.

Based on these replies, it looks to be not worth the effort to eliminate all the error values.

Years ago, this spreadsheet was about half the size and it got to the point where it would take 5 minutes to calculate, so I cleaned it up a bit and reduced the total number of designs it was capable of. (It calculates a number of beams, columns, shear walls, etc in a wood-framed building or house)
 
There is supposedly a preferred directionality in the Excel calculation order, i.e., top to bottom, left to right etc. So, any calculations that go against that directionality will incur additional loops through the spreadsheet.

Your problem, of course, is hardly unique. My CFO from a previous job was mightily excited about getting the first IBM 386-class computer in the division because it was absurdly sluggish. Sure enough, the new computer whizzed through the spreadsheet. However, after about a month, the sheet got absurdly sluggish again, because the CFO added all the functionality he couldn't run before.

Have you looked at how much memory you have? DRAM is way cheaper than a new computer, and if you're churning on virtual memory, that can be seriously slow for all concerned.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize
 
electricpete,

Thanks, I will research those.


IRstuff,

My computer is WinXP, Pentium 4 CPU @ 3 GHz, only 1 gHz of RAM. Maybe that's the problem.

I pulled out a small portion of one worksheet and gutted it to eliminate all links outside that worksheet, and all defined names, just to show what I meant in previous posts.
 
 http://files.engineering.com/getfile.aspx?folder=d3b29004-8c22-4c58-8501-961cb1fe2008&file=REF!_ERROR.xlsx
These days memory is pretty cheap. You might try upgrading depending on the number of slots in your machine. I'd wager that you can double your DDR RAM for under $100.
 
OK, now I did the "go to last cell" and one worksheet had a problem in that regard - I copied and pasted to blank worksheet, but all the defined names on that worksheet were killed (#REF!).

What is the correct procedure? Fortunately, I save each new version under a new name so I didn't screw up the worksheet.

 
Disregard previous post - I did a cut and paste instead...that works OK.
 
One thing I've done where I have a lot of rows with the same formulas is to make all but the first row values (Copy, then Paste|Special|Values). When I need to add more data or update the values, I copy the first row down and redo the values.

Another way to speed things up is to put calculation on manual, at least for tables. You can make changes faster then when you're done recalculate.
 
did you try clearing the contents of all the unused cells?
I get a slow down on several files and once i clear all the contents of the rows and columns that seem to have no data, it speeds up greatly
 
I hope I'm not causing offense here, but to cover the basics:

- How much free space do you have on the hard drive?
- When's the last time you defragged?
- Can you reduce the number of other applications running in the background?

"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
 
Excel used to learn where you were working on the sheet and save the sheet accordingly. You could have a 5x5 block of data starting in A1, but if you had at some point by accident ctrl-down arrowed to the 65k row and typed a few letters, Excel would then save down the whole block no matter if you deleted it later. Filesize would compound and the sheet would run retarded - a small problem in a small sheet, but would seem to go crazy with big sheets.

Not sure what the new file formats have done about this but some sparse math seems to have sorted it out. I just checked with the example above. In the new formats the test block was 8.08k, and 8.26k with the 65k cell full. Saving the same files into excel 97-2003 formats went to 17k and 41k respectively. Deleting the text in the errorsome cell reduced the new files back down, but the old format stayed at 41k.

 
Pete,

This is good info!

Thanks

D23
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor