Forum Discussion
excell
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
- That is great , It has solved my problem thank you for your help
6 Replies
- 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/corrupted-excel-file-repair?forum=excel]- That is great , It has solved my problem thank you for your help
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.
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,
- 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.
