Getting this msg "Excel ran out of resources while attempting to calculate one or more formulas."

[GaneshIyer]

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.

[#2]

@TanmayB19 , file is not shared, I can't access it.

[#3]

@Sergei Baklan  I try to check and I realize that cell D36 is clear in all my documents. In my case, that message appears every time I open excel, and every time I type anything, so annoying! I'm not using excel for any formula. I even went there and format the cell as text.  This issue just started and I don't know why. If you could help me, I will really appreciate it. 

[#4]

@Sergei Baklan 

I am having a similar problem.  What is the quickest way to find this problem?

[#5]

@Sergei Baklan 

Hey, I'm suddenly having this issue, I do have one crazy sheet full of sumifs formulas but I don't think this is an end-of-game excel workload. Can you please take a look at my spreadsheet? The sheet that gives trouble is "DCF Flujo". You can trigger the problem by cutting rows 179:183 and pasting them at row 143. That's what Im trying to do and can't.

Nutuuk EcoEstudios 221101 DCF 

Much appreciated!

[#6]

Hey there, thanks for all your support in this thread / issue.  how would I go about identifying these types of formulas myself. I have a file that's fairly large and in which I cannot remove sensitive info to give to you. @Sergei Baklan 

[#7]

Sergei Baklan

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?

New and Improved Sales KPI.xlsx

[#8]

@Sontiyi 

I'm not sure which exactly formula gives an error, but if you add in AU1 next month (Jan 01, 2023) it works.

[#9]

@BSinisi 

It depends. If you have Inquire in your version of Excel you may do Workbook Analysis and check suspicious array formulae. Not necessary that give an answer. Otherwise delete your sheets / ranges one by one till such error disappears. Other words, try to localize it.

[#10]

@Jaegerter 

Sorry, I'm not able to open your workbook. It says

Try to localize an issue removing sheets/ranges till an error disappears.

[#11]

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

[#12]

@Sergei Baklan How did you find the circular reference? I have a similar issue in my workbook, but xcel "can't show it"

Thanks

[#13]

@Sergei Baklan here is the file that is getting this error.  Can you help me locate the problem?

[#14]

@Sergei Baklan 

I have really strange problem with my spreadsheet. It works normally with Excel 2016. But in corporate version of Office 365 the same spreadsheet  shows "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated" I have tried to change number of calculation threads in Advanced option, I used 1 and used 2. After entering 2 - message disappear, but formulas do not calculated at all. Maybe you have any ideas ?

Thank you!

Best regards, Yuliya

Link to file:

https://drive.google.com/uc?export=download&id=1DHQL7EkWosmAz4-bo6cC0eVmB17INhvi 

[#15]

@Jaegerter 

Not sure what it was exactly. I simply Del few dozens of rows after #181 (they looks like empty) in hidden sheet Lag by  Week. Calculation error disappears.

[#16]

@Yuliya1610 

Unfortunately link doesn't work for me

[#17]

@Sergei Baklan 

Hello, I am sorry. I attached a new link.

[#18]

@Sergei Baklan  I have no idea how you located the problem but all I can say is thank you,  thank you!!!!

[#19]

@Jaegerter 

You are welcome, glad to help

[#20]

Hi @Yuliya1610 ,

Error appears in InventoryReport sheet with formulae in column G (Qty)

=SUMIFS(
    ProducedGoodsTracker!$D$3:$D$10000,
    ProducedGoodsTracker!$B$3:$B$10000, $B4,
    ProducedGoodsTracker!$A$3:$A$10000, "<" & $D$1
) -
    SUMIFS(
        SalesTracker!$D$3:$D$10000,
        SalesTracker!$B$3:$B$10000, $B4,
        SalesTracker!$A$3:$A$10000, "<" & $D$1
    )

if D1 (start date) is blank. Don't know what is exact reason, perhaps will return back to that some later. As workarounds you may

- remove data validation from D1, enter any date, e.g. 1900-01-01 instead of blank and apply data validation again. However, if someone Del on D1 an error appears again.

- more reliable to modify formula like

=SUMIFS(
    ProducedGoodsTracker!$D$3:$D$10000,
    ProducedGoodsTracker!$B$3:$B$10000, $B4,
    ProducedGoodsTracker!$A$3:$A$10000, "<" & IF( ISBLANK($D$1), 0, $D$1 )
) -
    SUMIFS(
        SalesTracker!$D$3:$D$10000,
        SalesTracker!$B$3:$B$10000, $B4,
        SalesTracker!$A$3:$A$10000, "<" & IF( ISBLANK($D$1), 0, $D$1 )
    )

Both variants are in attached, sheets protection is removed.

[#21]

Thank you so much !!! I am really grateful !!!
I corrected my spreadsheet and sent it to the person with corporate Office 365, I will receive an answer how it works maybe in several days.
(I can suppose, when Excel does not see the exact date, less than which the value should be, it goes looking for it at the very beginning of time ))) , and this generates such an error. It's strange why it only appears in 365 office and does not in 2016.
Thank you for attachments, I downloaded variant with formula.
If it is possible, could you please delete attachment now (because I don't want this file to be freely available on the Internet).
Best regards, Yuliya