Forum Discussion
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.
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.
SergeiBaklan - Amazing it worked well for me. Thanks a million.
Happy Holidays and Happy New Year in advance!
Thanks and Happy Holidays to you too!
Any chance you could help me figure out whats wrong with my workbook? I am getting the message as well. I am a complete novice at excel but am trying to put together a workbook to calculate show estimates/costs for venue events. I have been reading lots of answers on how to fix this error but cant figure it out. Any help is appreciated!
I see no problems with your file, it works correctly (at least without mentioned error).
SergeiBaklan hi! I have the same issue. How do i find the cell containing the error?
Thank youFirst issue in 2021
Second one in 2019
I change both on 2021-12-13m have no ide what it shall be.
- Cannot move from cell to cell without an error message showing- 'excel ran out of resources'. Can you please advise??????
SergeiBaklanI am also getting that error. Could you please take a look and advise/ fix anything you see wrong.
- Thanks so much for your speedy help and diagnosis!
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.
- @sergei bakln thank you this is so very helpful. what did you do to find the problems? they were ruining my experience of excel.
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 to am having the same error message, any assistance would be much appreciated
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.
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.
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.
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.
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.
- 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.
