Problem with Slow ReCalculation of Dynamic Range Using OFFSET

[Guest]

About a year ago I created a giant spreadsheet that took a large download
from a Financial database, and then sorted it into various categories across
time, using a series of SUMIF functions that specified whole columns (almost)
Row 2 to 65536.
And the whole spreadsheet would recalculate itself within about 5 - 10
minutes (getting slower with the increase in data downloaded each month).

However, over the last year I have learnt a lot of useful tips from this
discussion group and various links to other webpages.
One of which says that it is Better to use Dynamic Ranges to specify
columns/areas of data that change size by defining a Named Range that uses
the OFFSET( , , ,COUNTA()) formula.
Which has worked great for other spreadsheets I have created recently,
especially in combination with the SUMPRODUCT() formula.

However, when I tried to improve upon my original spreadsheet that was using
the SUMIF formulaes by replacing them all with a SUMPRODUCT() with Dynamic
Ranges the sheet takes absolutley forever to recalculate.

Could someone give me some advice as to whether it is better to
Revert back to my original SUMIF type formulas
OR
Use the SUMPRODUCT with Non-Dynamic Ranges (ie Row 2 to Row 65536)
OR
Use the SUMPRODUCT with Non-Dynamic Range with a Find & Replace Macro to
Specify the Columns Ranges (although when I have run a Find and Replace
manually it takes about 30mins to run - there are a lot of repetitive
formulae)
OR
Something Else that reduces the Volatility of the Formulae (this was cited
as a major contributor to slowing recalculations down)
I presume that the Named Dynamic Ranges get recalculated everytime a Cell
recalculates, which is the reason the revised spreadsheet runs significantly
slower.
If this is the case, is there a method to overcome this problem??

Thanks for any help that you can give.

Kris

 

[Niek Otten]

Hi Kris,

Just one thought

Consider to calculate in a macro. That macro would also have to create the
correct Defined Name (and delete the old definition first, of course). So
technically it is not a dynamic name, but it will be adjusted each time you
calculate, as long as you use just the macro for that.

 

[Guest]

Thanks for the Advice

I have added a macro to Update the Named Ranges at a click of a button, and
the calculation time has reduced.

However, it still takes it time to recalculate.
Also, I have rechecked how long it takes to Recalculate in the old SumIf
spreadsheet, and the new spreadsheet using SumProduct
- and I probably exagerated in my original post the old SumIf spreadsheet
calculates in about 1-2 minutes (it seemed longer from memory)
and the new SumProduct sheet with the Static Ranges takes about 12-15
minutes (down from the 30min mark with the Dynamic Ranges) which is still far
too long when it needs to be refreshed after each change to input data.

Does anyone have any ideas to speed up the spreadsheet further?
Or should I revert back to my SumIf formulae, which to me seemed a far more
complex solution, unlike the SumProduct that was neat and tidy and would of
been easier for anyone else using the spreadsheet to follow (and maintain)
The SumProduct is being used to evaluate upto 5 conditions, whereas before
the SumIf worked by Concatenating the conditions into a single cell and then
comparing against the concatenated criteria.

Thanks for any more help that anyone can give.

Kris