Forum Discussion

GaneshIyer's avatar
GaneshIyer
Copper Contributor
Dec 19, 2020

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."...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 19, 2020

    GaneshIyer 

    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's avatar
    SergeiBaklan
    MVP
    Sep 03, 2021

    steph88 

    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.

     

  • Stephane_Zwel's avatar
    Stephane_Zwel
    Copper Contributor
    Nov 07, 2021

    steph88 

     

    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 ! N266
     
     
     
     Replace 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.