Dynamic Ranges Slowing Workbook Calculations - Options to Remedy?

[grant95783]

First post here, hope I document this appropriately.

I have a large workbook that utilizes 20 or so dynamic ranges. When I change a value in this workbook it calculates pretty slowly. While dynamic ranges are important for this workbook, I've noticed that they add significant calculation time to the workbook. For example just typing a value in any cell that doesn't affect downstream calculations still has lag due to calculations. I'm almost certain this is because dynamic ranges are volatile, so every time any cells is activated, the workbook calculates to make sure the dynamic ranges haven't changed.

I've attached 2 workbooks, one using dynamic vs one using static ranges. The "Data to be populated" tab has a button that runs some code that changes some input cells to trigger calculations. The tab then captures the calculation time info for a full calculation (Ctrl+Alt+F9), Recalculate (F9), and each sheet calculation time (test for yourself if you want). These 2 workbooks are identical except one uses dynamic ranges and the other static. The dynamic workbook has a higher volatility ratio as well as significantly higher calc time, almost 5x slower. You can easily see the difference if you have the calculation set to automatic and just type random numbers in any cell in both workbooks. While they both are always recalculating, the one with dynamic ranges has a noticeable lag while the static range file seems instantaneous.

These workbooks are a base example of my much larger workbook. I generally only load the tables initially when I open the files (ie table 1 and 2 get loaded) but possibly could adjust them while in the file. I'm wondering if others have had this issue and what their proposed solution is? These are my ideas:

1. Best Option - make the "Tables" sheet, which host the dynamic ranges, not calculate after being populated. This way the dynamic ranges are not constantly being calculated to check if the range changes. So in essence the dynamic range is calculated once, then it acts the same as static ranges going forward. Only if you go in that sheet and change the tables should the dynamic range recalculate. I've tried setting these sheets to disable calculation in the VB properties for the dynamic range sheets and this didn't work.
2. Workable but not ideal Option - have VB code that makes dynamic ranges, calculates them, then converts them to static ranges. Somewhat problematic if changing tables more than once.

I've been investigating this for weeks to figure out what is slowing down the workbook. Now that I've found the problem, I'm hoping someone much smarter than I has an answer. I appreciate any help.

Cheers,
Grant

[TMS]

I'm not a huge fan of using OFFSET for Dynamic Named Ranges and always use INDEX in preference. OFFSET is a volatile function, whereas, INDEX is not.

I used:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

I ran your Static test 5 times: 46%, 41%, 49%, 48% and 53%

I ran your Dynamic test 5 times: 101%, 101%, 101%, 100% and 100%

And finally, I ran my version of your Dynamic test 10 times: 3%, 2%, 3%, 2%, 3%, 2%, 3%, 2%, 3%, 2%

The percentage will, I guess be affected by what other workbooks are open at the time.

Your code leaves the workbook/Excel needing to re-calculate ... I'm not sure if that is intentional.

I have attached a copy of the modified workbook.


Regards, TMS

[TMS]

You could also try converting the "tables" to real Tables.

This version calculates volatility around 40% to 45% however, the calculation appears to be much, much quicker.

On that basis, I'm not sure of the relevance of your volatility calculation.

Anyway, another option to explore.

[grant95783]

Thanks so much TMS. I really appreciate the time and help.

Index is definitely better than offset for dynamic ranges. While tables is better, the only possible concern is that while the table expands appropriately, it doesn't contract unfortunately. This would be more problematic if the table feeds a dropdown list. The blanks would then be present in the drop down. The dynamc range would adjust for this, always a tradeoff.

I use the volatility ratio to help to show if much of the workbook is recaculating regardless of whether an input into a cell has any dependencies. From the microsoft website:

8.Calculate workbook volatility as the ratio of recalculation time to full calculation time.

This measures the extent to which volatile formulas and the evaluation of the calculation chain are obstructions.


Thanks again,
Grant

[TMS]

while the table expands appropriately, it doesn't contract unfortunately

Yes, it does. But you would need to use the Delete icon on the ribbon rather than just pressing the Delete key. Excel maintains the Table address internally. You cannot edit it yourself.

The dynamic range would adjust for this, always a trade off

No, it doesn't. You're using COUNTA, in either case, to count the number of entries. If there are blanks, all it means is that you will lose the latter entries in the list (by the number of blank entries).


I'll take your word for the calculation. It is fairly obvious which methods give the best results.

Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.