Calculation Speed

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

 

[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

 

[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

 

[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!

 

[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!