How to speed up sluggish worksheet???

[Big Blue]

I have a 2000 row spreadsheet with some hefty formulae, including several with 6 nested if stmts. Whne data is entered, it takes a long time for the sheet to run through it's calculations. Anything I can do to spped it up (short of buying a faster CPU)?

 

[Jack in the UK]

Hi guys

To add to all these resources, im much for staying inside Excel and CORRECT was of using Excel, shes fine but when you ask her to carry that extra bag of shopping dinners late ( for all the sinics i say Excel is a girl as she beautiful and cooks better than me!)

Bt,, If your running a data base follow Chris D lead function are built in to Excel tru click function wizar and select dat base have a play, evem Dsum, or Dget will hyperdrive the cal time.

BTW no one has said they need the odd cairful planning ie reference cells and who to get then to work, 3 times as long to set up but a million times faster..

Not one to advertise, but a pal Dave Hawley really cuts the mustard if you need some examples, hes over on http://www.ozgrid.com, and some downloads, but please post your findings and where you failed and suceeded others will find this a wonder resource,

Just some learning to add to the wonderful advice..
HTH

 

[Aladin Akyurek]

On 2002-04-08 13:39, Big Blue wrote:
Thanks for effort, but I started with D...
and wound up with a horrendous spreadhseet
imagine subtotalling 3 categories of items, by 8 departments, for every week of the year excluding holidays... I had to use arrays

If possible, restrict the range the array formulas look at dynamically computed item or department ranges.

Aladin

 

[Jack in the UK]

Hi Aladin,

You have stolen my line so im proud you agree withthis post always a battle between the VBA programers and open formula "Guys"

Arrays are cool but easy i say not to many else Slooow to high light Aladins comments again.

Look at other ways Aladin say name range.. this is the way to go pure and simple She beautiful and sexy if you name range her.... excel. try and see.. i bet you love her....

 

[Dave Hawley]

Hi Big Blue

I would bet money that your sloooowww Workbook is due to the use of array formulas. I get many clients with the same problem and 99% of the time it's array formulas.

Array formulas are only a very superficial approach to a problem and are normally suggested by people offering nothing more than quick fix solutions.

The Database functions in Excel can be used in place of array formulas in MOST cases. They are designed specifically for working with LARGE amounts of data, something array formulas are no good for. They do take longer to set up and plan, but that's the way it is when doing a spreadsheet correctly. If you go here:
http://www.ozgrid.com/download/default.htm and download: "DFunctionsWithValidation.zip" you will see some uses of these Functions. Normally it requires having hidden rows and/or Columns for your criteria.

You should also use dynamic named ranges for range referencing in most formulas as this also helps a LOT. http://www.ozgrid.com/Excel/DynamicRanges.htm

Bottom line Big blue is that the planning of a spreasheet is often more time consuming than setting up, when done efficiently.

Do yourself a huge favour and use arrays ONLY to pull out some data, then delete them, or better still avoid them altogether. Take the time to learn Pivot Tables, Database function, Subtotals etc

I do feel for you Big Blue, as I have seen this time and time again. Unfortunately while Q&A forums like this are brilliant, there will always be the odd few that give poor advise.

 

[Big Blue]

Thanks to all, especially Dave Hawley. Around here, I'm considered an Excel guru, but I know enough to realize how little I know. I'm taking the advice and exploring Pivot Tables and Dynamic Ranges. Always willing to learn. Thanks again.

 

[Dave Hawley]

Hi Big Blue

Hope you master these excellent features.