excell

[Maurice_Patton]

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

[#2]

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

[#3]

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

[#4]

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

[#5 · Accepted solution]

That is great , It has solved my problem thank you for your help

[#6]

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.

[#7]

@NikolinoDE 

Thank you I have fixed the problem