SOLVED

Getting error "Getting error "Excel ran out of resources...."

%3CLINGO-SUB%20id%3D%22lingo-sub-2186035%22%20slang%3D%22en-US%22%3EGetting%20error%20%22Getting%20error%20%22Excel%20ran%20out%20of%20resources....%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186035%22%20slang%3D%22en-US%22%3E%3CP%3EGetting%20error%20%22Excel%20ran%20out%20of%20resources%20while%20trying%20to%20calcululate%20on%20or%20more%20formulas.%20As%20a%20result%20these%20formulas%20cannot%20be%20evaluated.%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoing%20fairly%20big%20job%20with%20many%20tabs%20and%20columns%20and%20formulas%2C%20and%20that%20popped%20up.%20I%20went%20thru%20all%20the%20errors%20on%20the%20formulas%26gt%3Berror%20dropdown%2C%20mostly%20reference%20errors%2C%20but%20still%20no%20luck.%20Finally%20I%20just%20made%20a%20safe%20copy%20of%20one%20sheet%20and%20got%20rid%20of%20every%20formula%2C%20and%20still%20same%20error.%20Then%20I%20copied%20and%20pasted%20entire%20sheet%20as%20%22Value%22%20just%20to%20be%20doubly%20sure%20no%20formulas%2C%20and%20same%20error.%20What%20the%20heck%3F%20I%20uninstalled%20Microsoft365%20and%20reinstalled%20with%2064%20bit%20version%2C%20and%20tried%20disabling%20multithreading%2C%20but%20no%20luck.%20It%20does%20seem%20to%20work%20when%20I%20switch%20to%20manual%20calculation%2C%20but%20that%20is%20no%20good%20for%20a%20large%20project%20with%20constant%20changes.%3C%2FP%3E%3CP%3EAny%20ideas%3F%20Thanks.%3C%2FP%3E%3CP%3EEDIT%20Addl%20info%3C%2FP%3E%3CP%3E8GB%20RAM%2C%20900G%20storage%20of%20which%2075%25%20available%2C%2064%20bit%20operating%20system.%20No%20problem%20showing%20in%20task%20manager%20for%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20is%20only%205%20MB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20it%20completely%20stripped%20down%20to%20just%20numbers%2C%20and%20no%20formula..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2186035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2186232%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20error%20%22Getting%20error%20%22Excel%20ran%20out%20of%20resources....%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186232%22%20slang%3D%22en-US%22%3EI%20can%20open%20it%20without%20issue.%20As%20you%20said%2C%20it's%20just%20data%2C%20so%20it%20certainly%20shouldn't%20be%20giving%20you%20this%20issue.%20Perhaps%20you%20had%20a%20ram%20module%20go%20bad%20and%20you%20don't%20actually%20have%208GB%20available%20(assuming%20you%20probably%20have%202%20ram%20modules%20-%20Win10%20will%20technically%20run%20on%204%2C%20but%20it's%20not%20recommended%20and%20would%20surely%20cause%20a%20lot%20of%20resource%20issues)%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20believe%20you%20could%20test%20it%20with%20this%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.memtest86.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.memtest86.com%2F%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2186360%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20error%20%22Getting%20error%20%22Excel%20ran%20out%20of%20resources....%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186360%22%20slang%3D%22en-US%22%3E%3CP%3EThanks...that%20memtest%20sound%20good%20but%20I%20think%20it%20takes%20quite%20a%20while%3F%20I%20did%20quick%20Crucial%20scan%20which%20checks%20for%20available%20upgrades%20from%20them%2C%20and%20my%20PC%20scored%20above%20average.%20And%20I%20have%20worked%20with%20huge%20excel%20files%20on%20here-%20a%20million%20rows%2C%20but%20not%20much%20computation%20in%20those%2C%20just%20storage%20really.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20other%20excel%20files%20are%20working%2C%20but%20none%20have%20a%20formula%20as%20complicated%20as%20this%20type%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(W2%3D%22%22%2C%22%22%2CSUM(W2%2BH%246))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20appears%20in%20approx%201000%20cells%20in%20the%20sheet%2C%20in%20a%20few%20columns.%20BUT%20even%20when%20I%20delete%20all%20instances%20of%20this%20formula%2C%20I%20get%20the%20error.%20At%20this%20point%20even%20if%20I%20enter%20the%20number%201%20into%20a%20blank%20cell%20the%20error%20pops%20up.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ECan%20an%20excel%20file%20get%20corrupted%20by%20a%20formula%2C%20and%20the%20corruption%20persist%20even%20after%20formula%20removed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2186871%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20error%20%22Getting%20error%20%22Excel%20ran%20out%20of%20resources....%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186871%22%20slang%3D%22en-US%22%3EI'm%20afraid%20I'm%20at%20bit%20of%20a%20loss%20as%20to%20what%20the%20problem%20could%20be.%20The%20file%20you%20uploaded%20has%20not%20given%20me%20any%20errors%2C%20so%20I'm%20not%20able%20to%20reproduce%20the%20problem.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

10 Replies
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?

best response confirmed by Philster (Occasional Contributor)
Solution
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.
Belated thank you. I am rebuilding the sheet- not a huge deal so not worth the effort to track down error.

BTW interesting thing I should mention is I realized when that error prone file was open, and I opened another excel file while the error file stayed open, the same problem started occuring in the other excel file. When I closed the error file, the other excel files behave fine. Bizarre,
I'm sorry I couldn't be of more help. I've never had a workbook misbehave in the manner you describe and, unfortunately, the sample workbook didn't act up for me. Hopefully, rebuilding it takes care of it. Good luck.

@Philster 

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.

@Philster 

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.

image.png

Not sure what did you mean here, but if remove this formula

image.png

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.

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.

@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.