ctrl+shift+f9 (forced calculation) does not work

[Damokles]

HI,


I am a trader who wants to do some backtesting with excel. It went fine until yesterday.
I discovered that my workbook won't finish its calculations, each time I press f9, shift+f9, ctrl+shift+f9 or change calculations mode from manual to automatic and vice versa it starts to calculate up to approximately 30% and then disrupts the process. Only if I edit the formula or press f2 and then enter, it updates its value. During the calculations my processor usage spikes each time (at 100% if I delete some formulas it spikes to 60%) and then settles on 15% until I press enter or click somewhere on excel, then it goes to 2% and the calculation process has definitely stopped.
I even tried to replace "=" with "=" in all formulas but it got stuck on cell PD7904 (sheet: XX), I could not find out why. all cells are formatted as numbers, dates or generall, there are no circular references, calculation is enabled on VBA, no errors besides some irrelevant ones at the very beginning. It is worth noting that it is a very large and complex workbook because I want to test several strategies simultaneously. Is it really possible that I might have pushed Excel to its boundaries? If I delete most of the formulas it works again, but up until yesterday, It could handle it. Could it be possible that my processor got heat damaged during those 100% spikes?
In attachment you find the file, perhaps you can tell if there are too many formulas, I use the 2016 64- bit version.
If I can overcome this obstacle by buying better hardware could you recommend something?


If you can help me out, I would be very grateful.


Below you find my configuration:


OS Name Microsoft Windows 10 Home
Version 10.0.17763 Build 17763
System Type x64-based PC
Processor Intel(R) Core(TM) i7-7700K CPU @ 4.20GHz, 4201 Mhz, 4 Core(s), 8 Logical Processor(s)
Baseboard Manufacturer ASRock
BaseBoard Product Z270 Pro4
Installed Physical Memory (RAM) 16.0 GB DDR4 2.4GHz
Available Physical Memory 11.4 GB
Total Virtual Memory 27.6 GB
SSD 250GB 520/540 850 EVOBasic SA3 SAM


Link for file download (83 MB)


https://www.dropbox.com/s/bp4miwgc8ph23ad/Backtesting_sample.xlsx?dl=0

 

[Yongle]

Perhaps this thread will help
https://www.mrexcel.com/forum/excel...workbook-running-very-slow-less-than-5mb.html

 

[Damokles]

Thank you for your link, I am having a look on this, this may take some time because those workbook metrics show only the model size even if I run it on his sample workbook... I am also testing if I can put each strategy in a seperate sheet and try if sheet calculations work, maybe I can found the source in that way

 

[Damokles]

So I solved this and the solution is pretty disturbing because one must be very cautious with this:
So the last thing I edited was a function wich I changed so it became an array function, but while doing so I creted an iterative calculation, because it two cells on the same row referenced to each other, but it never got recognised as such, so I did not think it is an issue although in fact it was. So my personal conclusion is that if array functions are involved in iterative calculations theyre not recognised as such, which in my case proved very dangerous.

 

[Yongle]

Glad you sorted it and thanks for that useful warning in your feedback