×
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

Sluggish workbook

Sluggish workbook

Sluggish workbook

(OP)
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?

RE: Sluggish workbook

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.

RE: Sluggish workbook

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.

RE: Sluggish workbook

(OP)
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)

RE: Sluggish workbook

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: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

RE: Sluggish workbook

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

RE: Sluggish workbook

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.

RE: Sluggish workbook

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

 

RE: Sluggish workbook

(OP)
Disregard previous post - I did a cut and paste instead...that works OK.

RE: Sluggish workbook

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.
 

RE: Sluggish workbook

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

RE: Sluggish workbook

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

RE: Sluggish workbook

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.

  

RE: Sluggish workbook

Pete,

This is good info!

Thanks

D23

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