Excel ran out of resources while attempting to calculate one or more formulas.

%3CLINGO-SUB%20id%3D%22lingo-sub-2807693%22%20slang%3D%22en-US%22%3EExcel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807693%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20see%20attachment%20(screenshot).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20correct%20my%20formulas%20so%20this%20message%20does%20not%20appear%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2807693%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2807796%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807796%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20might%20be%20able%20to%20avoid%20the%20error%20message%20about%20running%20out%20of%20resources%20by%20closing%20all%20workbooks%20and%20add-ins%20that%20you%20aren't%20using.%20You%20can%20also%20help%20yourself%20by%20using%20PivotTables%20instead%20of%20building%20reports%20using%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20scrollbar%20travel%20extends%20far%20beyond%20the%20bottom%20of%20your%20data%2C%20delete%20those%20blank%20rows%2C%20save%20the%20workbook%2C%20close%20the%20workbook%2C%20then%20reopen%20it.%20The%20scrollbar%20travel%20should%20now%20match%20the%20data%20in%20your%20worksheet.%20You%20should%20notice%20a%20reduction%20in%20file%20size%20as%20well%20as%20snappier%20recalculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20using%2064-bit%20Office%3F%20If%20not%2C%20you%20should%20be.%20The%2032-bit%20Office%20is%20limited%20to%20a%20maximum%204%20GB%20of%20memory%2C%20even%20if%20you%20have%20more%20installed%20on%20your%20computer.%20This%20memory%20pool%20must%20be%20shared%20between%20Excel%2C%20add-ins%20and%20open%20workbooks.%2064-bit%20Excel%20can%20use%20all%20the%20memory%20you%20have%20on%20your%20computer%20(the%20limit%20is%20thousands%20of%20gigabytes).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20check%20whether%20you%20have%2032%20or%2064%20bit%20using%20the%20File...Account...About%20Excel%20ribbon%20item.%20The%20first%20line%20of%20the%20resulting%20dialog%20should%20look%20something%20like%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EMicrosoft%C2%AE%20Excel%C2%AE%20for%20Microsoft%20365%20MSO%20(Version%202110%20Build%2016.0.14509.20000)%2064-bit%3CSPAN%20class%3D%22%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20have%2032-bit%20Excel%20and%20want%2064-bit%2C%20uninstall%20Office%20then%20reinstall%20it%20making%20sure%20to%20specify%20that%20you%20want%2064-bit%20Office.%20You%20won't%20lose%20any%20email%20or%20settings%20while%20doing%20this.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20saw%20%22Calculate%22%20in%20the%20status%20bar%20at%20the%20bottom%20of%20your%20Excel%20window%2C%20so%20I%20know%20you%20have%20such%20a%20complicated%20set%20of%20workbooks%20that%20Excel%20can't%20maintain%20its%20calculation%20tree.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20screenshot%20in%20your%20attached%20Word%20document%2C%20I%20see%20a%20formula%20with%20far%20more%20parentheses%20than%20are%20needed.%20The%20extras%20should%20be%20deleted%20for%20clarity.%20Was%20there%20a%20sale%20on%20parentheses%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20was%20comparing%20the%20results%20of%20the%20DAY%20function%20to%20365.%20The%20DAY%20function%20returns%20the%20day%20of%20the%20month--a%20number%20between%201%20and%2031.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20above%20suggestions%20aren't%20solving%20your%20problem%2C%20please%20post%20your%20workbook%20for%20more%20specific%20suggestions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Please see attachment (screenshot).

 

How can I correct my formulas so this message does not appear?

 

Thank you.

 

 

9 Replies

You might be able to avoid the error message about running out of resources by closing all workbooks and add-ins that you aren't using. You can also help yourself by using PivotTables instead of building reports using formulas.

 

If the scrollbar travel extends far beyond the bottom of your data, delete those blank rows, save the workbook, close the workbook, then reopen it. The scrollbar travel should now match the data in your worksheet. You should notice a reduction in file size as well as snappier recalculation.

 

Are you using 64-bit Office? If not, you should be. The 32-bit Office is limited to a maximum 4 GB of memory, even if you have more installed on your computer. This memory pool must be shared between Excel, add-ins and open workbooks. 64-bit Excel can use all the memory you have on your computer (the limit is thousands of gigabytes).

 

You can check whether you have 32 or 64 bit using the File...Account...About Excel ribbon item. The first line of the resulting dialog should look something like:

Microsoft® Excel® for Microsoft 365 MSO (Version 2110 Build 16.0.14509.20000) 64-bit 

 

If you have 32-bit Excel and want 64-bit, uninstall Office then reinstall it making sure to specify that you want 64-bit Office. You won't lose any email or settings while doing this.

 

I saw "Calculate" in the status bar at the bottom of your Excel window, so I know you have such a complicated set of workbooks that Excel can't maintain its calculation tree.

 

In the screenshot in your attached Word document, I see a formula with far more parentheses than are needed. The extras should be deleted for clarity. Was there a sale on parentheses?

 

Your formula was comparing the results of the DAY function to 365. The DAY function returns the day of the month--a number between 1 and 31.

 

If the above suggestions aren't solving your problem, please post your workbook for more specific suggestions.

@mlhinds4973 

Why do you think that's this formula causes an error? Usually such errors appears due to wrong use of dynamic array formulae, especially when you shift from former version of Excel on one which supports dynamic arrays.

This error is coming up on ALL of my workbooks. I have deleted formula data. I have deleted smaller not important workbooks. I have checked and traced errors. I cannot find them. I have a 64 bit office already. The error pops up every time I add something new or open a workbook.

@heavenlydough 

Could you share one of such workbooks removing sensitive information?

@Sergei Baklan 

Okay, so the other day when I asked this question, it was doing it on all of my workbooks like previously stated. But right now, it is only doing it on one workbook. However i have removed a lot of unnecessary data. I have checked for errors on all my sheets and removed or ignored them all.

 

I went to the highlight "credit tip" column pictured and changed all those formulas to the number the formula created and it says it is an error and says "This cell in inconsistent with the column formula. " maybe this has something to do with it.

 

In some cells, I have formulas like this "=14.98+15.57" instead of "=SUM(14.98+15.57)" maybe that is why?Screenshot (104).pngScreenshot (106).png

It gives me this error when I try to exit out of the workbook as well.

@heavenlydough 

I don't think above are reasons, something else.

Do you have an idea of what the problem would be?