Calculation Speed

[ColleenK]

I post this before and implemented the suggestions, to no avail, so I am
asking for further ideas.

This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of
the following formulas: sumif, sumproduct, vlookup and embedded if
statements. This takes about 10 minutes to calculate and when I hit "save
as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with
1 GB of Ram, does anyone have any suggestions as to how I can speed up the
calculations?

 

[Sean Timmons]

the calcs will take as long as they take... if the formulas are to external
workbooks, ensure they are open when you go to save.

Perhaps it's a matter of either separating these 15 tabs into several
workbooks or finding ways to use less formulas....

 

[Elkar]

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[ColleenK]

Thanks I will give that a try.
--
CK

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[Sean Timmons]

And an embedded if can be a bit disconcerting as well.. Dependong on how many
layers we're looking at.......

 

[T. Valko]

the SUMIF and COUNTIF functions are not very

efficient for calc times. I always use SUMPRODUCT instead.

Are you sure you don't have that backwards?

 

[Elkar]

I'm quite certain. Set up some test data and try it out for yourself. A
quick test using 2 columns of 65000 rows each, the SUMIF function noticeably
hangs for a few seconds while calculating. The SUMPRODUCT function is
virtually instant.

 

[T. Valko]

Hmmm...

That's interesting. I can assure you that a general SUMIF/COUNTIF formula is
significantly more efficeint than the equivalent SUMPRODUCT formula.

Here's what I did...

Filled A1:A65000 with random letters A or B.
Filled B1:B65000 with random numbers 0 to 100.

Using Charles Williams RangeTimer method to measure the calculation time:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Average of 5 calculations for each formula:

COUNTIF and the equivalent SUMPRODUCT

=COUNTIF(A:A,"A") = 0.007 secs
=SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs
=SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs

=COUNTIF(B:B,">=50")-COUNTIF(B:B,">75") = 0.011 secs
=SUMPRODUCT((B1:B65000>=50)*(B1:B65000<=75)) = 0.120 secs
=SUMPRODUCT(--(B1:B65000>=50),--(B1:B65000<=75)) = 0.123 secs

SUMIF and the equivalent SUMPRODUCT

=SUMIF(A:A,"A",B:B) = 0.009 secs
=SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs
=SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs

 

[Elkar]

Very interesting indeed. Using your examples, the calc time is better with
SUMIF. However, I tried filling column A with =ROW(), column B with
=RIGHT(A1,1) and Column C with =RAND().

In D1, I placed the formula: =SUMIF(B1:B65535,"2",C1:C65535)

It takes over 10 seconds to calculate.

I then placed an apostrophe in front of D1 to "disable" it. Then, in D2 I
placed the formula: =SUMPRODUCT((B1:B65535="2")*C1:C65535)

It takes less than 1 second.

Repeated the process with the same results.

So, I'm going to play around with this more, but apparently other factors
have influence on the performace of SUMIF and SUMPRODUCT.

 

[T. Valko]

Now that is indeed interesting!

I don't know why that does that and I'll have to consult a real expert on
that one but I suspect (know) the RAND function is involved somehow. If you
convert column C to constants the SUMIF version is faster to calculate than
the SUMPRODUCT version.

Although the sample you describe does what it does, it's *not* because SUMIF
is less efficient than SUMPRODUCT. If you time just the SUMIF formula and
compare its calc time to just the SUMPRODUCT version you'll see that the
SUMIF version is faster to calculate, about 0.054 secs vs 0.058 secs. All
that other time is taken by the RAND() functions recalculating.

Let me see what I can find out.

 

[Harlan Grove]

Now that is indeed interesting!

I don't know why that does that and I'll have to consult a real expert on
that one but I suspect (know) the RAND function is involved somehow. If you
convert column C to constants the SUMIF version is faster to calculate than
the SUMPRODUCT version.

....

Speculation, but it sure seems that SUMIF triggers recalc after
accessing each cell in its 1st argument. SUMIF and COUNTIF give the
same abysmal recalc performance against a large range filled with
formulas calling TODAY. If so, the rule is simple: use SUMIF and
COUNTIF on ranges containing constants or formulas calling no volatile
functions; use SUMPRODUCT on ranges containing formulas calling
volatile functions. When in doubt, use SUMPRODUCT because it'll be
slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be
slow by TENS OF SECONDS when they're suboptimal.

 

[Bill Ridgeway]

the calcs will take as long as they take... if the formulas are to
external
workbooks, ensure they are open when you go to save.

Perhaps it's a matter of either separating these 15 tabs into several
workbooks or finding ways to use less formulas....

CK wrote <<Perhaps it's a matter of either separating these 15 tabs into
several workbooks>> A downside of this is that any structural changes in a
workbook will not be referred to correctly in another workbook and any
recalculation will be incorrect. Move just one cell and everything could go
to pot!

Bill Ridgeway.

 

[T. Valko]

Here's a reply from Charles Williams:
--------------------
Its a calculation chain sequence problem: in this case rather extreme.

Assuming that you are using manual calculation mode you will find that the
second time you use F9 the SUMIF calculation is much faster. This is because
the second F9 recalculation reuses the optimised calculation chain sequence
from the first calculation.

You can also check this (in manual calc mode) by
a) Enter all the formulae (SUMIF but not SUMPRODUCT)
b) reenter (by copy down) column a, then col b then col C. F9 is now fast
c) re-enter the SUMIF formula, F9 is now slow the first time and fast the
second time

This is because re-entering a formula places it at the top of the
calculation chain.

When Excel attempts to calculate a formula it gets rescheduled if it depends
on uncalculated cells. So in the slow SUMIF case it is being rescheduled
thousands of times, and in the fast SUMIF case its already in the optimum
place in the calc chain so only gets calculated once.

With SUMPRODUCT the rescheduling does not seem to happen thousands of times:
I don't know why (maybe something to do with it being handled like an array
formula?)

regards
Charles

 

[Charles Williams]

I think I have narrowed it down to Array formulae (& SUMPRODUCT) containing
calculated virtual columns versus ordinary formulae.

If you take the example test problem (I only used 10000 rows)

In Manual calc Mode
col A=ROW()
col B=Right(col A,1)
col C=Rand()
enter these formulae

Then if you enter =SUM(A1:A10000,C1:C10000) in D3 and press F9 it takes 4.9
seconds on my system
Pressing F9 a second time takes less than a millisecond

If you enter {=SUM(A1:a10000*C1:C10000)} as an array formula in D3 and press
F9 it takes 0.007 seconds
Pressing F9 a second time takes the same time

If you array enter {=SUM(A1:A10000,C1:C10000)} it also takes 4.9 seconds

Conclusion: forcing Excel to calculate a virtual column which references
uncalculated cells bypasses the multiple recalculation of formulae which
would otherwise happen.

So here is a case where SUMPRODUCT and Array formulae are substantially
faster than ordinary formulae!

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

 

[T. Valko]

So here is a case where SUMPRODUCT and Array

formulae are substantially faster than ordinary formulae!

Good stuff! I just hope I'll be able to remember this.

Thanks Charles!