Speeding up Excel calculations

[JP Ronse]

Hi All,

I have a workbook with a lot of array functions. To give an idea:
- 17 sheets of which 15 with about 150 array functions per sheet
- 1 sheet contains the raw data, > 10,000 rows
- I have to use array functions because several conditions have to be
checked

It is all working fine, the only thing is that it takes 30 minutes or more
to recalculate the workbook. So, you can imagine that I try to avoid
recalculations as much as possible.

Does one have some good tips to speeding up Excel?

With kind regards,

JP

 

[Don Guillett]

Smaller ranges to check??
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Charles Williams]

Some things to try are:
Move conditions that are used in more than one array formulae out of
the array formulae into helper columns.
Sort the data and calculate start and end rows (or start rows and
counts) for each subset block of data, then restrict your array
formulae to the subsets.
See if you can use pivot tables instead of your array functions.

for more ideas see my website and in particular
http://www.decisionmodels.com/optspeedj.htm

regards
Charles

 

[JP Ronse]

Hi Don,

Thanks for your offer but I really want to be able to find it out myself.

Wkr,

JP

 

[JP Ronse]

Hi Charles,

Thanks for the tips. Found already some good hints.

Wkr,

JP

 

[JP Ronse]

To be more concret.

One of functions I'm using is like this below:

{=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(EventHandler=$B$3)*(EventsHandled))}

NCTime, EventHandler & EventsHandled refers to Events!A2:A20000,
Events!B2:B20000, Events!C2:C20000

NCTime contains a date, EventsHandler a string and EventsHandled are
figures.

$B$1 is a year, e.g; 2009
D7..Dn: 1, 2, 3, ... 12
$B$3 like "JP"

I defined the ranges big enough to be sure that I was not running out of the
scope of the range when adding new data so that my functions will still
return the correct values.

At the end of the year, I found out that NCTime is about A2:A308.

So, looking for a way to take only a valuable range in account. I've had a
look on Chip Pearson's page about dynamic ranges but was not able to figure
it out. Any help will be very appreciated.

With kind regards,

JP

 

[David Biddulph]

How does the speed of that array formula compare with using SUMPRODUCT?

=SUMPRODUCT((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(EventHandler=$B$3)*EventsHandled)
or (perhaps quicker)
=SUMPRODUCT(--(YEAR(NCTime)=$B$1),--(MONTH(NCTime)=D7),--(EventHandler=$B$3),*EventsHandled)

 

[Stan Brown]

You may or may not "have to use array functions". Consider
SUMPRODUCT, which is much, much faster and can "check multiple
conditions" just fine.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Don Guillett]

Then try what Charles (an expert in the field) and I said about smaller
ranges to lookup. You can name blocks and use sumproduct for the named
blocks.

 

[T. Valko]

You can make it slightly faster by eliminating one array of tests:

(YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)

(TEXT(NCTime,"myyyy")=D7&B1)

Using the double unary might also save a tick or two.

 

[T. Valko]

You may or may not "have to use array functions".

SUMPRODUCT, which is much, much faster

Not necessarily.

Which of these do you think calculates faster:

=SUMPRODUCT(--(A1:A20000=1),--(B1:B20000=2),--(C1:C20000=3),D120000)

Array entered:

=SUM(IF(A1:A20000=1,IF(B1:B20000=2,IF(C1:C20000=3,D120000))))

You're right if you said the SUM(IF array.

 

[JP Ronse]

Hi All,

You gave a lot of useful suggestions. I'll try them. Thanks a mot.

With kind regards,

JP