Apr 25 2021 07:41 AM
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
Apr 25 2021 11:26 AM
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.
Apr 25 2021 11:56 PM
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,
Apr 26 2021 04:24 AM
Apr 26 2021 05:45 AM
SolutionApr 26 2021 09:44 AM
Apr 26 2021 05:45 AM
Solution