Does anybody know the reason for the lag before calculation in a large workbook? And potentially how to resolve...
Specifically, when I change a particular input it takes a minute to complete the calculation - during that minute I can look at the Processes tab of Task Manager in Windows and can see for the first 57 seconds Excel is Not Responding and the CPU % doesn't change. In the last 3 seconds the CPU % shoots up and the calculation progress appears at the bottom of Excel.
What's happening in the first 57 seconds? Is Excel assessing calculation chains perhaps? The worksheet has approx 18 million cells with formula (which I appreciate is a lot) but only about 50 unique formula (calculations are in 360k-cell blocks) and so I wouldn't have thought the calculation chain is particularly complex. Calculations themselves aren't particularly complex either and no volatile functions / arrays / sumproduct etc.
It feels like Excel is assessing the volume of the task for a long time (57 secs) and then quite quickly does the job at the end (3 secs)... I appreciate reducing the volume of formula will probably speed things up but I'm interested in understanding the reason for/what's happening during the initial lag.
Any thoughts would be appreciated.
Dan
Specifically, when I change a particular input it takes a minute to complete the calculation - during that minute I can look at the Processes tab of Task Manager in Windows and can see for the first 57 seconds Excel is Not Responding and the CPU % doesn't change. In the last 3 seconds the CPU % shoots up and the calculation progress appears at the bottom of Excel.
What's happening in the first 57 seconds? Is Excel assessing calculation chains perhaps? The worksheet has approx 18 million cells with formula (which I appreciate is a lot) but only about 50 unique formula (calculations are in 360k-cell blocks) and so I wouldn't have thought the calculation chain is particularly complex. Calculations themselves aren't particularly complex either and no volatile functions / arrays / sumproduct etc.
It feels like Excel is assessing the volume of the task for a long time (57 secs) and then quite quickly does the job at the end (3 secs)... I appreciate reducing the volume of formula will probably speed things up but I'm interested in understanding the reason for/what's happening during the initial lag.
Any thoughts would be appreciated.
Dan