Speeding up Excel calculations
 

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

Speeding up Excel calculations
 

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JP Ronse" wrote in message
...
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

Speeding up Excel calculations
 

Some things to try a
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

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

Speeding up Excel calculations
 

Hi Don,

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

Wkr,

JP


"Don Guillett" wrote in message
...
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JP Ronse" wrote in message
...
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

Speeding up Excel calculations
 

Hi Charles,

Thanks for the tips. Found already some good hints.

Wkr,

JP

"Charles Williams" wrote in message
...
Some things to try a
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

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

Speeding up Excel calculations
 

To be more concret.

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

{=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$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



"Charles Williams" wrote in message
...
Some things to try a
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

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

Speeding up Excel calculations
 

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)
--
David Biddulph


"JP Ronse" wrote in message
...
To be more concret.

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

{=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$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



"Charles Williams" wrote in message
...
Some things to try a
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

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

Speeding up Excel calculations
 

Sun, 28 Feb 2010 15:29:18 +0100 from JP Ronse :
- I have to use array functions because several conditions have to be
checked

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


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

Speeding up Excel calculations
 

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JP Ronse" wrote in message
...
Hi Don,

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

Wkr,

JP


"Don Guillett" wrote in message
...
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JP Ronse" wrote in message
...
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

Speeding up Excel calculations
 

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

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

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

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

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

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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)
--
David Biddulph


"JP Ronse" wrote in message
...
To be more concret.

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

{=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$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



"Charles Williams" wrote in message
...
Some things to try a
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

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

Speeding up Excel calculations
 

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.

--
Biff
Microsoft Excel MVP


"Stan Brown" wrote in message
t...
Sun, 28 Feb 2010 15:29:18 +0100 from JP Ronse :
- I have to use array functions because several conditions have to be
checked

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


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

Speeding up Excel calculations
 

Hi All,

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

With kind regards,

JP

"T. Valko" wrote in message
...
You can make it slightly faster by eliminating one array of tests:

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

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

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

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

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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)
--
David Biddulph


"JP Ronse" wrote in message
...
To be more concret.

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

{=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$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



"Charles Williams" wrote in message
...
Some things to try a
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

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