Forum Discussion
Getting error "Getting error "Excel ran out of resources...."
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
- I'm afraid I'm at bit of a loss as to what the problem could be. The file you uploaded has not given me any errors, so I'm not able to reproduce the problem.
10 Replies
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)
- Thank you. Yes, I had whole column formulas in there that only applied to a few hundred row. That was probably the problem...but strangely it persisted even when I removed the column long formulas. I just rebuilt to sheet- only took an hour, so was less work then tracking down error. Thanks again.
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.
- I can open it without issue. As you said, it's just data, so it certainly shouldn't be giving you this issue. Perhaps you had a ram module go bad and you don't actually have 8GB available (assuming you probably have 2 ram modules - Win10 will technically run on 4, but it's not recommended and would surely cause a lot of resource issues)?
I believe you could test it with this
https://www.memtest86.com/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?
- I'm afraid I'm at bit of a loss as to what the problem could be. The file you uploaded has not given me any errors, so I'm not able to reproduce the problem.
