How do I reduce the calculating time in a workbook?

Don't over use Arrays, don't use Arrays with Large Ranges ... don't use Volatiles (see the Volatile link in my sig) & avoid repetitive calculations.

I've seen one of your formulae on the other thread which is an array and uses enormous ranges ... with arrays it is imperative you limit ranges as much as possible
(use Dynamic Named Ranges if necessary - see http://www.contextures.com/xlNames01.html)

Take a look here http://www.decisionmodels.com/ it's Charles Williams' (MVP) site, i have just been to an Excel Users Conference where he gave a tutorial on speeding up Excel's calculations, he has some great stuff there.

I suspect we were at the same conference (@ Victoria?) .. I went specifically on the Thurs to hear his talk in the afternoon... (and have finally put his site available via my Sig (to Volatile initially).. I reference the site so often it's not true)

Originally Posted by DonkeyOte

I suspect we were at the same conference (@ Victoria?) .. I went specifically on the Thurs to hear his talk in the afternoon... (and have finally put his site available via my Sig (to Volatile initially).. I reference the site so often it's not true)

Yes, i went both days! pictures can be seen at here http://picasaweb.google.co.uk/xld.bo...InI_OSvi8iHgQE#

Manual calculation will obviously improve performance if the file is slow to calculate ... particularly if you have Volatile Arrays... however it does not solve the underlying problem(s).

Bjornar's suggestions are certainly good ones IMO... as referenced previously (via my sig) you should certainly work through Charles Williams' site - it is generally regarded as the place to go for any/all Calculation related queries... the author is on the steering committee for MS re: new versions etc... ie he knows his stuff.

I would pretty much stake my house on the fact that your arrays are the cause of the problem... without seeing your file it's very hard for us to show you how to correct the issues... rarely are Arrays needed in Volume - using Concatenation and SUMIF/COUNTIF will have an immense impact on the performance of your file... there are a lot of examples of this on the forum... Array should be used very sparingly.

In short post a sample file.

OK, I've had a quick look through it and yes your high volume of arrays (and immense ranges) are undoubtedly the cause of the problem...

I have for your benefit reviewed and replaced the expensive formulae with less expensive formulae... the file should now run on Auto Calc instantaneously.

Notes:

-- The headers in the columns containing formulae that need to be corrected have been highlighted red so you can see where changes made...

-- The old formulae were placed either above or below the data in the same column so you can compare

-- The single condition SUM(IF CSE's have been replaced with simple SUMIF (as discussed on your other thread)

-- The multi condition SUMPRODUCT(s) have been replaced with COUNTIFs utilising Wildcard approach and a concatenation column which I added to your Tracking sheet in Column AB


EDIT: I forgot to remove the Module1 from the VBE which is a simply Range.Calculate method (so you can calc only a specific range of cells at a time... this was for testing and can be removed... unless removed you may get prompted to Enable Macros)


Let us know your results given revised approach.