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

Damokles

New Member
Joined
Sep 27, 2019
Messages
3
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Damokles

New Member
Joined
Sep 27, 2019
Messages
3
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

New Member
Joined
Sep 27, 2019
Messages
3
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it and thanks for that useful warning in your feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,314
Messages
5,635,506
Members
416,861
Latest member
Breadnjam18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top