Forum Discussion
Slow updates
I have a spreadsheet with 3 tabs. I input data into 1 and it populates the other 2...
Only 7 items get populated to one tab and 3 to the other, yet it takes over 30 secs for the 2 tabs to update - this was never so slow in Win 7 & previous Excel...
Any ideas please?
An issue is in formula in cell N134 of Logbook
=SUMIF(A:A,">"&TODAY()-28*(E:E))Here for each cell in column A you use 1 million criteria (all cells in column E) to sum, thus formula makes 1M*1M = 1e12 calculations. After that it tries to return 1M results, but have not enough space for it and returns #SPILL error.
In previous versions of Excel silent implicit intersection mechanism worked in the background, formula calculated only for the criteria from current row.
Not sure what exactly you'd like to calculate, simply disabled the formula. Please check attached file if it is updated correctly.
11 Replies
An issue is in formula in cell N134 of Logbook
=SUMIF(A:A,">"&TODAY()-28*(E:E))Here for each cell in column A you use 1 million criteria (all cells in column E) to sum, thus formula makes 1M*1M = 1e12 calculations. After that it tries to return 1M results, but have not enough space for it and returns #SPILL error.
In previous versions of Excel silent implicit intersection mechanism worked in the background, formula calculated only for the criteria from current row.
Not sure what exactly you'd like to calculate, simply disabled the formula. Please check attached file if it is updated correctly.
- Excellent! Thank you Sergei (spasibo!) Yes, that (A:A, E:E) fixed the slow response...
Now I am curious... why did Nikolino report "For me, your file works without problems, quickly and does not hang anywhere." ???
Best regards,
PaulPaul, you are welcome. Perhaps Nikolino is on Excel which doesn't support dynamic arrays (e.g. Excel 2019). If so mentioned silent implicit intersection works and it shall be no such issue. Such behavior helps sometimes, but in general could be source of other errors if use it incorrectly, plus dynamic arrays provide lot of new possibilities to build more reliable Excel projects in shorter time.
- For me, your file works without problems, quickly and does not hang anywhere.
Here is my suggested solution ...
File> Options> Advanced> Formulas> Use all processors on the computer: (click the radio button)
So it should actually be faster ... if it wasn't clicked 🙂
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.- Thanks Nikolino,
I already had that ticked... no improvement 😞
BR,
P- Try this too:
Formulas> Name Manager
Delete all referenceless links
= #REFERENCE! #REFERENCE!
Especially the #REFERENCE! errors with links.
I hope that I could help you with this information / links.
