Speeding up Excel calculations

J

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
 
D

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.
 
C

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
 
J

JP Ronse

Hi Don,

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

Wkr,

JP
 
J

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
 
D

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)
 
D

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

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

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),D1:D20000)

Array entered:

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

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

JP Ronse

Hi All,

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

With kind regards,

JP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top