Speeding up calculations

[sb1920alk]

I have about 10 columns whose calculations use the sumproduct formula based
on the entries in most of the previous columns. So the 10th column has about
6 critieria in the sumproduct formula. In an ideal world, each of the
critieria would refer to thousands of rows, but this makes the calculations
very slow. Even limiting it to 500 rows, there is still a ~1 second delay.

I would like the file to be able to hold thousands of entries, but not waste
time calculating empty rows.

Do you think it would help the calculation speed if I set up dynamic named
ranges to use as references in my sumproduct calculations? Or do you think it
would take even longer because the Excel would need to repeatedly calculate
the size of the dynamic range?

I appreciate any input on this.

Thanks,

 

[Don Guillett]

edit>name>define>name it something like myrng>in the refers to box
=offset($a$1,0,0,counta($A:$A),6)
look at the help index for INDEX to see how to adjust your offset formula.
=sumproduct((myrng="a" etc

 

[galimi]

Uncertain if this helps, but I've pasted information below on the Dirty
method. This may speed up calculation times.

Dirty Method
See AlsoApplies ToExampleSpecificsDesignates a range to be recalculated when
the next recalculation occurs.

expression.Dirty
expression Required. An expression that returns one of the objects in the
Applies To list.

Remarks
The Calculate method forces the specified range to be recalculated, for
cells that Microsoft Excel understands as needing recalculation.

If the application is in manual calculation mode, using the Dirty method
instructs Excel to identify the specified cell to be recalculated. If the
application is in automatic calculation mode, using the Dirty method
instructs Excel to perform a recalculation.

Example
In this example, Microsoft Excel enters a formula in cell A3, saves the
changes, and then recalculates cell A3.

Sub UseDirtyMethod()

MsgBox "Two values and a formula will be entered."
Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Formula = "=A1+A2"

' Save the changes made to the worksheet.
Application.DisplayAlerts = False
Application.Save
MsgBox "Changes saved."

' Force a recalculation of range A3.
Application.Range("A3").Dirty
MsgBox "Try to close the file without saving and a dialog box will
appear."

End Sub

 

[Jim Thomlinson]

If you are going against thousands of rows there is probably not much you can
do... You can use dynamic named ranges but since they rely on the offset
function this will make all of your sumproduct formulas volatile (recalculate
every time there is a calculation whether the dependant range has been
dirtied or not). That being said you may already have more than 65,536
dependancies at which point Excel stops doing it's smart calc and
reclaculates everything anyways. The long and the short of it is that this is
probably going to be slow. You can either turn calculations off and recalc as
necessary or you could just live with it... (you could use code to make the
ranges dynamic without the offset function, but once again you may already be
over the 65,536 dependancies limit and Excel will have abandoned doing smart
calcs so this route may have little or no effect).

 

[Niek Otten]

Just as an addition to what others have posted already:

Look at

www.decisionmodels.com


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have about 10 columns whose calculations use the sumproduct formula based
| on the entries in most of the previous columns. So the 10th column has about
| 6 critieria in the sumproduct formula. In an ideal world, each of the
| critieria would refer to thousands of rows, but this makes the calculations
| very slow. Even limiting it to 500 rows, there is still a ~1 second delay.
|
| I would like the file to be able to hold thousands of entries, but not waste
| time calculating empty rows.
|
| Do you think it would help the calculation speed if I set up dynamic named
| ranges to use as references in my sumproduct calculations? Or do you think it
| would take even longer because the Excel would need to repeatedly calculate
| the size of the dynamic range?
|
| I appreciate any input on this.
|
| Thanks,

 

[Pete_UK]

If your SUMPRODUCT formulae are referring to other cells in the same
row, then you could speed them up by joining columns together (with &)
and using SUMIF with a single criteria instead. The actual details will
depend on what you are doing currently.

Hope this helps.

Pete

 

[sb1920alk]

Not sure how to use this...

Uncertain if this helps, but I've pasted information below on the Dirty
method. This may speed up calculation times.

Dirty Method
See AlsoApplies ToExampleSpecificsDesignates a range to be recalculated when
the next recalculation occurs.

expression.Dirty
expression Required. An expression that returns one of the objects in the
Applies To list.

Remarks
The Calculate method forces the specified range to be recalculated, for
cells that Microsoft Excel understands as needing recalculation.

If the application is in manual calculation mode, using the Dirty method
instructs Excel to identify the specified cell to be recalculated. If the
application is in automatic calculation mode, using the Dirty method
instructs Excel to perform a recalculation.

Example
In this example, Microsoft Excel enters a formula in cell A3, saves the
changes, and then recalculates cell A3.

Sub UseDirtyMethod()

MsgBox "Two values and a formula will be entered."
Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Formula = "=A1+A2"

' Save the changes made to the worksheet.
Application.DisplayAlerts = False
Application.Save
MsgBox "Changes saved."

' Force a recalculation of range A3.
Application.Range("A3").Dirty
MsgBox "Try to close the file without saving and a dialog box will
appear."

End Sub

 

[sb1920alk]

That's what I'm suggesting. Do you think it will speed up the calculations vs
using a fixed range?

 

[sb1920alk]

Interesting. Is there any way to see if I'm already at 65,536?

Thanks,

 

[sb1920alk]

ok

 

[sb1920alk]

Well, the site answered part of my question:

Names are calculated each time they are referenced by a formula that is
recalculated

Thanks,