Mar 04 2021 12:54 PM - edited Mar 10 2021 05:05 AM
EDIT solution provided to me below. Thanks all.
Getting error "Excel ran out of resources while trying to calculate one or more formulas. As a result these formulas cannot be evaluated. "
Doing fairly big job with many tabs and columns and formulas, and that popped up. I went thru all the errors on the formulas>error dropdown, mostly reference errors, but still no luck. Finally I just made a safe copy of one sheet and got rid of every formula, and still same error. Then I copied and pasted entire sheet as "Value" just to be doubly sure no formulas, and same error. What the heck? How can I get an error on calculating formulas when there are no formulas?! I uninstalled Microsoft365 and reinstalled with 64 bit version, and tried disabling multithreading, but no luck. It does seem to work when I switch to manual calculation, but that is no good for a large project with constant changes.
Any ideas? Thanks.
EDIT Addl info
8GB RAM, 900G storage of which 75% available, 64 bit operating system. No problem showing in task manager for excel.
The file is only 5 MB
Mar 04 2021 01:54 PM
Mar 04 2021 02:41 PM - edited Mar 04 2021 02:43 PM
Thanks...that memtest sound good but I think it takes quite a while? I did quick Crucial scan which checks for available upgrades from them, and my PC scored above average. And I have worked with huge excel files on here- a million rows, but not much computation in those, just storage really.
My other excel files are working, but none have a formula as complicated as this type
=IF(W2="","",SUM(W2+H$6))
which appears in approx 1000 cells in the sheet, in a few columns. BUT even when I delete all instances of this formula, I get the error. At this point even if I enter the number 1 into a blank cell the error pops up.
Can an excel file get corrupted by a formula, and the corruption persist even after formula removed?
Mar 04 2021 07:57 PM
SolutionMar 09 2021 02:38 AM
Mar 09 2021 07:16 AM
Mar 09 2021 08:19 AM
Like Mr. Jeff Blakley (@ JMB17) You informed, I can only confirm that your file can also be opened with my Excel without problems and works perfectly.
It could probably have been array formulas.
If one does not limit the row area in an array formula, it can lead to considerable time delay.
Instead of whole columns like $ B: $ B, only use the actual cell ranges (e.g. $ B2: $ B150000).
This brings a considerable acceleration from the former approx. 3:30 minutes to approx. 10 seconds, the time difference can be considerable.
Here is some additional information from Microsoft, what could have been the reason.
"Excel cannot complete this task with available resources" error occurs in Excel 2010
I wish you continued having fun with Excel
Nikolino
I know I don't know anything (Socrates)
Mar 09 2021 08:37 AM
Mar 09 2021 01:28 PM
Most probably you are on Office365. In AM1 you have the formula which tries to return combinations of ranges from column A to column AM with intersection.
Not sure what did you mean here, but if remove this formula
it'll be no problems with your workbook at all, at least from performance point of view.
Not 100% sure, but on Excel without dynamic arrays most probably it shall be no problems with such combination.
Mar 10 2021 05:04 AM - edited Mar 10 2021 05:13 AM
You nailed it! Error is gone when I remove that.
Unfortunately I rebuilt the whole thing in last few days lol. Thanks again.
Edit I can't list this as "best response" since I already gave that to a guy above who tried hard.
Mar 10 2021 02:57 PM
@Philster , you are welcome.
No problem, I don't care about best responses. More important you know now what was the reason. Even if workbook is rebuilt that could help in future.
Mar 04 2021 07:57 PM
Solution