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

New Contributor

Please see attachment (screenshot).

 

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

 

Thank you.

 

 

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