I have a spreadsheet recording all my traded markets for over 3 years. It's up to almost 50,000 rows and in the last few weeks I have been getting occasional and temporary grey errors in calculation cells. They only last a few seconds before correcting themselves but I'm starting to worry if the integrity of my sheet is at risk? I've worked with Excel all my life and never seen such things before. I managed to grab a screenshot of one just now (below) which says "(24 threads): 0" although usually they contain the text "calculation". When it occurs it seems to effect all calculation cells in the row to the right of any new data input.
Anyone ever seen anything like this before?
Excel errors
-
jamesedwards
- Posts: 4789
- Joined: Wed Nov 21, 2018 6:16 pm
You do not have the required permissions to view the files attached to this post.
-
ShaunWhite
- Posts: 10605
- Joined: Sat Sep 03, 2016 3:42 am
That's bizarre. Whenever I get a glitchy sheet I save it as an xlsb (binary) or an older excel version then reload it and save as an xlsm again. Sometimes works. Either that or copy paste to a new spreadsheet?
Do a backup !
Do a backup !
-
jamesedwards
- Posts: 4789
- Joined: Wed Nov 21, 2018 6:16 pm
Have tried saving alternative versions but no luck. Managed to screengrab the full text this time. Looks like it's something to do with Excel struggling to calculate the cells quickly enough.
I already have 'multithreaded calculation' switched on (hence the 24 threads I assume?) and I'm running a super-powerful computer. I'll have to live with it and hope it doesnt get worse.
I already have 'multithreaded calculation' switched on (hence the 24 threads I assume?) and I'm running a super-powerful computer. I'll have to live with it and hope it doesnt get worse.
You do not have the required permissions to view the files attached to this post.
If the machine is entirely out of resources whilst calculating it may stick it into there but it's only I think this is only the third time I've ever seen it happen and the other two times the application.screenupdating had been set to false in the VBA
.
I'd suggest eliminating as many IFERROR, OFFSET and MATCH functions that you can, also any other lookup type functions due to them being volatile functions (iferror is surprisingly a big hog). Also potentially separate up your data into different sheets if you can.
I'm guessing it is in XLSX format already as you'll hit the 65535 row limit if it's XLS format soon enough.
.
I'd suggest eliminating as many IFERROR, OFFSET and MATCH functions that you can, also any other lookup type functions due to them being volatile functions (iferror is surprisingly a big hog). Also potentially separate up your data into different sheets if you can.
I'm guessing it is in XLSX format already as you'll hit the 65535 row limit if it's XLS format soon enough.