Help with reducing CPU demands?

[Matt Knight]

Hi there, I'm currently working on a costing model for education
courses for a college. Since I have 95 cost centres, some of which
contain courses and others are overheads, as well as course codes (of
which there are 602), I have loads of lookups (which I can probably
get rid of when I'm done) and sum products, index matches (which I
can't hard code, cos the model needs to be flexible to sensitivity
analysis). As you can expect, having all these formulae spread over
some 15 sheets has made the sheet pretty big (pushing 8meg in XML
format) and places heavy demands on the CPU (I'm using a quad core and
it's struggling)

What I could really do with is anything which may reduce the pressure
on the CPU as changes are made to the file (and this is before hitting
F9) without having to recode anything. I was just wandering if there
are any tricks to streamlining the calculation process as I don't
think my client will be so lucky as to enjoy lots of processing power!

Any help gratefully received.

Cheers
Matt (PS I checked out www.decisionmodels.com yesterday on the advice
of another thread on here, and it didn't give rise to anything I could
see being useful!)

 

[Charles Williams]

8 Meg is quite small, should not be a resource hog ...

If your problem is calculation time (F9 takes a long time when in Manual
Calc Mode) then:

For the Lookups and Index/Match sort your data and use the approx match
options of lookups and Matches
(see http://www.decisionmodels.com/optspeede.htm)

For SUMPRODUCT try splitting out helper columns, or using Pivot Tables, or
work with contiguous subsets of sorted data or ...
(see http://www.decisionmodels.com/optspeedj.htm)

If your problem is not calculation then its more difficult to offer advice

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Matt Knight]

It's only 8meg in .xlsx (Office Open XML formal) - in bog
standard .xls, I'm at about 80meg due to the lack of compression.

As far as using the suggestion from decisionmodels, it's something
I'll bear in mind next time I do something like this, but for now (and
with the timescale I'm on), the fuinctions are too deeply nested with
each other to pick apart and use hidden helper columns. Pivot tables
aren't really a valid option for what I want to do and how I want to
present the data. (but the links have given me lots to think about
with regards to future projects - added to bookmarks!)

Also, the actual calculation times seem to be okay - any reasonable
person would put up with them (usually less than 45seconds, though
I've not timed them, so it's probably significantly less). What is
weird, though, is after I've hit F9 and the calcs finish, if I amend a
cell, it takes about 3-5 mins (after appearing to go "unresponsive")
to make the change, without having done any calculations. I suppose
it's really this that I'm stuck on (apologies if this appears to
contradict my previous post!)

So I'm not holding out much hope for a solution, but any ideas,
suggestions, observations would be gratefully received!

Cheers
Matt

 

[Charles Williams]

If you get a long delay in Manual Calc mode when you update a cell and tab
to the next cell then I know of a few possible reasons:

- Google Desktop Office COM Addin: make sure that it has not sneakily
installed itself & get rid of it

- You have some event trapping code somewhere that is misbehaving.

- You have a very long and convoluted dependency tree (Excel updates the
dependency trees at each change to a cell). If you are using Excel 2007 you
may be able to bypass this problem by setting
Activeworkbook.ForceFullcalculation=True using VBA/VBE, but this will have
the side effect of making each calculation a Full calculation.


Might be worth trying .xlsb format rather than .xlsx - it will open and
close faster.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

It's only 8meg in .xlsx (Office Open XML formal) - in bog
standard .xls, I'm at about 80meg due to the lack of compression.

As far as using the suggestion from decisionmodels, it's something
I'll bear in mind next time I do something like this, but for now (and
with the timescale I'm on), the fuinctions are too deeply nested with
each other to pick apart and use hidden helper columns. Pivot tables
aren't really a valid option for what I want to do and how I want to
present the data. (but the links have given me lots to think about
with regards to future projects - added to bookmarks!)

Also, the actual calculation times seem to be okay - any reasonable
person would put up with them (usually less than 45seconds, though
I've not timed them, so it's probably significantly less). What is
weird, though, is after I've hit F9 and the calcs finish, if I amend a
cell, it takes about 3-5 mins (after appearing to go "unresponsive")
to make the change, without having done any calculations. I suppose
it's really this that I'm stuck on (apologies if this appears to
contradict my previous post!)

So I'm not holding out much hope for a solution, but any ideas,
suggestions, observations would be gratefully received!

Cheers
Matt

 

[Don Guillett]

Do you have large lookup or sumproduct ranges way beyond what is needed.
Perhaps change the formulas or break up the lookup ranges into smaller
defined names?? Just a thought.

 

[Matt Knight]

My lookup ranghes could probably be shortened quite a bit on a couple
of instances, as some of them are only v/hlooking up at the right/
bottom of 100 columns/600 rows. Once I've got the numbers to work,
I'll be brave enough to move them (it's been such a faff to get the
right numbers in the first place, I've been reluctant to move anything
until they get sorted!!)

Cheers for all the tips - on converting to a binary file, I'm noticing
improved performance/less throwing the toys out the pram!

Matt

 

[Matt Knight]

Just a quick update really - have finished the model and used the
suggestions made here and everything seems to work fine, with very
reasonable calculation times and much less throwing the toys out of
the pram whenever changes are made. Only problem is that the .xlsb
file won't open on our work laptops as they're using Office 2003.
Luckily the client is using Office 07, so it's only a minor
inconvenience!

Thanks again for the advice.
MK