Forum Discussion
Solved
Getting this msg "Excel ran out of resources while attempting to calculate one or more formulas."
I am having issue with excel sheet which keeps showing me this issue "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas can not be evaluated."
Never had seen this before- pls guide.
In cell D36 you have formula =30:294001 which means you instruct Excel to return all rows from 30 to 294001. Includes row in which the formula is. Kind of cyclic reference and never ended loop.
If you remove this formula it works.
Dear gane i have the same problem
how can I detect formula errors, unfortunately I cannot share the file as it belongs to my company. how can I detect formula errors, unfortunately I cannot share the file as it belongs to my company.
This spreadsheet worked fine for a long time now I constantly get the "run out of resources" message. Is there any help you can give with regard to what is causing this issue?
Sorry, I'm not able to open your workbook. It says
Try to localize an issue removing sheets/ranges till an error disappears.
- Sergei,
Could you check your private messages from me - I have the file there for you to download. Not sure why its not opening with that link...
- Why are there no move and copy buttons in the windows 11 toolbar ? ? ? The toolbar in the window 11 is useless !!!
- Sir, I am getting the "Excel ran out of resources error from the following file.Can you please help me rectify the same, further can you please explain how to diagnose the same.
I can do nothing without the file. If you have Inquire in your version of Excel, you may analyse workbook and check which array formula is wrong.
SergeiBaklan Can you help me with this one? A coworker is having the same error, but it just started happening. We cannot find out what's wrong.
SergeiBaklan Hi Sergei, appreciate all the help you provide here. I tried getting the inquire add-in to find this error myself, but not having any luck. Are you able to identify where the issue is in the attached spreadsheet? It is so simple and there are not any complicated formulas.
In D825, the formula is {=D795:DD764844:D803} which is non-sense, and most likely a typo.
Any chance you could help me figure out what's wrong with my workbook? I am getting the message as well.
I am trying to put together a stock dashboard. One of the functions I want to implement is to sort out my current holdings. But my formula (on Dashboard!B5, highlighted in yellow) returned the same error that everybody here did. Not sure why is it happening since there should be no cyclic referencing and the lady on Youtube could do it. Attaching the link for the Youtube tutorial as well. Many thanks in advance!
Not sure what you'd like to calculate, but this formula
=SORT(UNIQUE(FILTER(Ledger[Stock],SUMIF(Ledger[Stock],Ledger[Stock],Ledger[Position])<>0)))won't work, I guess internal cycling.
I to am having the same error message, any assistance would be much appreciated
Hello,
Regarding Ecology Planner 270821.xlsx, this file has two problems. One is circular references, a problem addressed by Sergei Baklan. Another is the "Excel ran out of resources ..." error and this problem is left unadressed so far.
Actually, the style reduction tool sees exactly one "slow calculation" in it, the one triggering the blocking error message in Excel.It's in September ! N266Replace the formula in it :{=_xlfn.SINGLE(INDEX($I$5:$I$239;SUMPRODUCT(MAX((N$5:N$239=BL266:$FI254266)*(ROW(N$5:N$239))))-ROW($I$5)+1))}by{=_xlfn.SINGLE(INDEX($I$5:$I$239;SUMPRODUCT(MAX((N$5:N$239=BL266:$FI266)*(ROW(N$5:N$239))))-ROW($I$5)+1))}and that fixes the problem. Hope it helps.It was a circular reference in November!J222. Change ranges here from IF(COUNTIF(J$5:J$230,... on IF(COUNTIF(J$5:J$220,...
Also in
December!J211
October!J195
September!N241
(all commented)
In November!J237 formula contains ...*(H242ROW(J$5:J$218).. . Commented it.
SergeiBaklan Hi. How do you find the formula errors in the spreadsheet? I'm getting the same error message as the original post. Thank you for your time.
BVuka Usually with Inquire, please see previous post here.
I am also having this error that never occured before i upgraded from Excel 11 recently. I am on a mac
I commented formulas in 'Food What'!C91 (circular reference) and in 'Payroll 2021'!AA40 (reference on entire column, '='C:\Users\LEFDPDirector\Downloads\[report1628717061286.xls]report1628717061286'!$1:$1048576)
Not sure which formulas shall be, but now there is no "out of resources" error.
SergeiBaklan Thank you so much this is very helpful. What is your process for finding the errors? This was ruining my experience of excel and would like to know what to do if it happens again.
