SOLVED

excell

%3CLINGO-SUB%20id%3D%22lingo-sub-2288287%22%20slang%3D%22en-US%22%3Eexcell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2288287%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20getting%20this%20message%20when%3CBR%20%2F%3Eworking%20on%20one%20of%20my%20worksheets%3CBR%20%2F%3E(%20excel%20ran%20out%20of%20resources%20while%20attempting%3CBR%20%2F%3Eto%20calculate%20one%20or%20more%20formula%2C%3CBR%20%2F%3Eas%20a%20result%2C%20this%20formula%20cannot%20be%20evaluated%20)%3CBR%20%2F%3EExcel%20cannot%20calculate%20a%20formula.%3CBR%20%2F%3EThere%20is%20a%20circular%20reference%20in%20an%20open%20workbook%3CBR%20%2F%3Ebut%20the%20refernce%20that%20cause%20it%20cannotbe%20listed%20for%20you.%3CBR%20%2F%3ETry%20editing%20the%20last%20formula%20you%20entered%3CBR%20%2F%3Eor%20removing%20it%20with%20the%20undo%20command%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2288287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2288733%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2288733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036212%22%20target%3D%22_blank%22%3E%40Maurice_Patton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20probably%20use%20many%20whole%20column%20matrix%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELittle%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20specified%20matrix%20formula%20could%20be%20reduced%20to%3A%20%7B%3D%20MAX%20((Table2!%20O%3A%20O%20%3D%20B3)%20*%20Table2!%20F%3A%20F)%7D%3C%2FP%3E%3CP%3ETest%20this%20with%20%3D%20MAX%20((Table2!%20O1%3A%20O40000%20%3D%20B3)%20*%20Table2!%20F1%3A%20F40000)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20including%20the%20whole%20column%20in%20the%20formula%2C%20add%20the%20range.%3C%2FP%3E%3CP%3EInstead%20of%20(as%20above)%20Table2!%20O%3A%20O%3C%2FP%3E%3CP%3ETest%20(as%20above)%20Table2!%20O1%3A%20O40000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20optimization%20for%20the%20calculation%20time%3A%3C%2FP%3E%3CP%3Eif%20the%20data%20source%20(table1%20%24%20A%3A%20%24%20E)%20contains%20a%20lot%20of%20data%2C%20then%3C%2FP%3E%3CP%3ETable1%20%24%20A%3A%20Sort%20%24%20E%20according%20to%20column%20A%20in%20ascending%20order%20on%20the%20whole%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditional%20Infos%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Favailable-resources-errors%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20troubleshoot%20%22available%20resources%22%20errors%20in%20Excel%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2290096%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2290096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Nikolino%20for%20your%20reply%2C%20I%20really%20do%20not%20understand%20your%20reply.%20All%20I%20would%20like%20to%20know%20is%20how%20to%20stop%20this%20popping%20up%20on%20my%20spread%20sheet%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291485%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291485%22%20slang%3D%22en-US%22%3EI%20was%20facing%20a%20similar%20problem%20where%20there%20were%20a%20few%20array%20formulas%20down%20about%20150.%3CBR%20%2F%3EThe%20reason%20is%20most%20likely%20that%20when%20the%20computer%20tries%20to%20process%20the%20large%20amounts%20of%20data%2C%20it%20uses%20multithreaded%20calculations.%20When%20multi-threaded%20calculation%20is%20turned%20to%20off%2C%20the%20computer%20doesn't%20throw%20the%20'Excel%20ran%20out%20of%20resources...'%20error.%3CBR%20%2F%3EThus%20to%20turn%20off%20multi-threaded%20calculation%2C%20follow%20the%20steps%20below%3A%3CBR%20%2F%3E%E2%80%A2%20Navigate%20to%20the%20'File'%20tab%20in%20your%20Excel%20sheet%20and%20select%20%E2%80%9COptions%E2%80%9D.%3CBR%20%2F%3E%E2%80%A2%20On%20the%20right%20side%20select%20%E2%80%9CAdvanced%E2%80%9D%20and%20scroll%20down%20to%20the%20heading%20%E2%80%9CFormulas%E2%80%9D.%3CBR%20%2F%3E%E2%80%A2%20You%20will%20see%20a%20check%20box%20that%20says%20Enable%20multi-threaded%20calculation.%20Untick%20it%2C%20then%20select%20'OK'%20and%20recalculate%20your%20formulas.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20this%20helps!%3CBR%20%2F%3E%3CBR%20%2F%3EOther%20than%20this%2C%20if%20you%20face%20any%20kind%20of%20.xls%20or%20.xlsx%20file%20corruption%20in%20Excel%20you%20can%20check%20out%20this%20reference%20%5B%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2FLync%2Fen-US%2Fe5692c4a-924d-44a5-985b-7878f3eb26de%2Fcorrupted-excel-file-repair%3Fforum%3Dexcel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2FLync%2Fen-US%2Fe5692c4a-924d-44a5-985b-7878f3eb26de%2Fcorrupted-excel-file-repair%3Fforum%3Dexcel%3C%2FA%3E%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291841%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291841%22%20slang%3D%22en-US%22%3EThat%20is%20great%20%2C%20It%20has%20solved%20my%20problem%20thank%20you%20for%20your%20help%3C%2FLINGO-BODY%3E
New Contributor

I keep getting this message when
working on one of my worksheets
( excel ran out of resources while attempting
to calculate one or more formula,
as a result, this formula cannot be evaluated )
Excel cannot calculate a formula.
There is a circular reference in an open workbook
but the refernce that cause it cannotbe listed for you.
Try editing the last formula you entered
or removing it with the undo command

6 Replies

@Maurice_Patton 

You probably use many whole column matrix formulas

 

Little details.

 

Your specified matrix formula could be reduced to: {= MAX ((Table2! O: O = B3) * Table2! F: F)}

Test this with = MAX ((Table2! O1: O40000 = B3) * Table2! F1: F40000)

 

Instead of including the whole column in the formula, add the range.

Instead of (as above) Table2! O: O

Test (as above) Table2! O1: O40000

 

Here is an optimization for the calculation time:

if the data source (table1 $ A: $ E) contains a lot of data, then

Table1 $ A: Sort $ E according to column A in ascending order on the whole formula.

 

Additional Infos: How to troubleshoot "available resources" errors in Excel

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE 

Thank you Nikolino for your reply, I really do not understand your reply. All I would like to know is how to stop this popping up on my spread sheet, 

I was facing a similar problem where there were a few array formulas down about 150.
The reason is most likely that when the computer tries to process the large amounts of data, it uses multithreaded calculations. When multi-threaded calculation is turned to off, the computer doesn't throw the 'Excel ran out of resources...' error.
Thus to turn off multi-threaded calculation, follow the steps below:
• Navigate to the 'File' tab in your Excel sheet and select “Options”.
• On the right side select “Advanced” and scroll down to the heading “Formulas”.
• You will see a check box that says Enable multi-threaded calculation. Untick it, then select 'OK' and recalculate your formulas.

I hope this helps!

Other than this, if you face any kind of .xls or .xlsx file corruption in Excel you can check out this reference [https://social.technet.microsoft.com/Forums/Lync/en-US/e5692c4a-924d-44a5-985b-7878f3eb26de/corrupte...]

best response confirmed by Maurice_Patton (New Contributor)
Solution
That is great , It has solved my problem thank you for your help
With the existing Aray formulas in your file, instead of searching through the entire column, you could restrict them to a more restricted area with the formula.
This means that it does not have to calculate all cells in the column (s).
The amount of data is decreasing.

@NikolinoDE 

Thank you I have fixed the problem