Excel ran out of resources while attempting to calculate one or more formulas. How to fix this?

%3CLINGO-SUB%20id%3D%22lingo-sub-2597010%22%20slang%3D%22en-US%22%3EExcel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20How%20to%20fix%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597010%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20issues%20with%20excel%20sheet%20which%20keeps%20showing%20me%20this%20issue%20%22Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20As%20a%20result%2C%20these%20formulas%20can%20not%20be%20evaluated.%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHow%20can%20I%20fix%20this%20nothing%20works%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2597010%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2622692%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20How%20to%20fix%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2622692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EStill%20nothing%20helped.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597373%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20How%20to%20fix%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117331%22%20target%3D%22_blank%22%3E%40dustin255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20opened%20it%20in%20Excel%20Desktop%20and%20Excel%20for%20web.%20In%20both%20cases%20it%20works%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597099%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20How%20to%20fix%20t%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117331%22%20target%3D%22_blank%22%3E%40dustin255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20information%20could%20help%20you.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Favailable-resources-errors%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20troubleshoot%20%22available%20resources%22%20errors%20in%20Excel%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Fexcel-cannot-complete-task-with-available%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EExcel%20cannot%20complete%20this%20task%20with%20available%20resources%22%20error%20occurs%20in%20Excel%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day.%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2623228%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20How%20to%20fix%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2623228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117331%22%20target%3D%22_blank%22%3E%40dustin255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20now%20it%20is%20reproduced%20on%20my%20PC.%20An%20error%20is%20floating%20-%20could%20appear%2C%20could%20not.%20I'm%20not%20100%25%20sure%2C%20most%20probably%20since%20you%20don't%20work%20with%20spills%20as%20with%20objects%20but%20calculate%20based%20on%20separate%20cells%20values%20within%20the%20spill.%20Excel%20starts%20such%20calculations%2C%20but%20STOCKHISTORY%20is%20still%20updating%20caches.%20Perhaps%20it's%20starts%20cycling%20calculations%20inside%20and%20we%20have%20an%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAbove%20only%20guess%2C%20But%20in%20any%20case%20I'd%20start%20working%20with%20spills%20as%20with%20objects.%20Some%20calculations%20could%20be%20done%20within%20spill%2C%20some%20with%20reference%20on%20it%20as%20entire%20object.%3C%2FP%3E%0A%3CP%3EWeekly%20prices%20and%20related%20percentage%20could%20be%20calculated%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20history%2C%20STOCKHISTORY(B1%2CQ2%2CQ3%2C%201%2C%200%2C%200%2C%201)%2C%0A%20%20k%2C%20SEQUENCE(ROWS(history))%2C%0A%20%20IFERROR(CHOOSE(%7B1%2C2%2C3%2C4%7D%2C%0A%20%20%20%20%20INDEX(history%2Ck%2C1)%2C%0A%20%20%20%20%20INDEX(history%2Ck%2C2)%2C%0A%20%20%20%20%20INDEX(history%2Ck%2C2)%2FINDEX(history%2Ck-1%2C2)-1%2C%0A%20%20%20%20%20INDEX(history%2Ck%2C2)-INDEX(history%2Ck-1%2C2)%20)%2C%0A%20%20%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EDaily%20prices%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(history%2C%20STOCKHISTORY(B1%2CQ2%2CQ3%2C0%2C%201%2C%200%2C%202%2C%203%2C%204%2C%201%2C%205)%2C%0A%20%20headers%2C%20%7B%22Price%20Change%22%2C%22Change%22%2C%22Low%2FHigh%20Change%22%7D%2C%0A%20%20r%2C%20ROWS(history)%2C%0A%20%20c%2C%20COLUMNS(history)%2C%0A%20%20cc%2C%20c%2B3%2C%0A%20%20k%2C%20SEQUENCE(r%2Ccc)%2C%0A%20%20i%2C%20INT((k-1)%2Fcc)%2B1%2C%20j%2C%20MOD(k-1%2Ccc)%2B1%2C%0A%20%20res%2C%20IF(i%3D1%2C%20IF(j%26gt%3Bc%2C%20INDEX(headers%2C%20j-c)%2C%20INDEX(history%2C%20i%2C%20j))%2C%0A%20%20IF(j%3D7%2C%20INDEX(history%2Ci%2C5)%2FINDEX(history%2Ci-1%2C5)-1%2C%0A%20%20IF(j%3D8%2C%20INDEX(history%2Ci%2C5)-INDEX(history%2Ci-1%2C5)%2C%0A%20%20IF(j%3D9%2C%20INDEX(history%2Ci%2C3)-INDEX(history%2Ci%2C4)%2C%20%20%20INDEX(history%2C%20i%2C%20j%20)%0A%20%20))))%2C%0A%20%20IFERROR(res%2C%22%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOther%20calculations%20using%20INDEX()%3AINDEX()%20to%20select%20ranges%20from%20spills%2C%20not%20with%20reference%20on%20cells%20within%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%20An%20error%20still%20could%20appear%2C%20I%20change%20only%20couple%20of%20spills%2C%20you%20have%20more%20plus%20bunch%20of%20other%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am having issues 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."


How can I fix this nothing works?

4 Replies

@dustin255 

I opened it in Excel Desktop and Excel for web. In both cases it works correctly.

@dustin255 

Yes, now it is reproduced on my PC. An error is floating - could appear, could not. I'm not 100% sure, most probably since you don't work with spills as with objects but calculate based on separate cells values within the spill. Excel starts such calculations, but STOCKHISTORY is still updating caches. Perhaps it's starts cycling calculations inside and we have an error.

 

Above only guess, But in any case I'd start working with spills as with objects. Some calculations could be done within spill, some with reference on it as entire object.

Weekly prices and related percentage could be calculated as

=LET(
  history, STOCKHISTORY(B1,Q2,Q3, 1, 0, 0, 1),
  k, SEQUENCE(ROWS(history)),
  IFERROR(CHOOSE({1,2,3,4},
     INDEX(history,k,1),
     INDEX(history,k,2),
     INDEX(history,k,2)/INDEX(history,k-1,2)-1,
     INDEX(history,k,2)-INDEX(history,k-1,2) ),
  ""))

Daily prices

=LET(history, STOCKHISTORY(B1,Q2,Q3,0, 1, 0, 2, 3, 4, 1, 5),
  headers, {"Price Change","Change","Low/High Change"},
  r, ROWS(history),
  c, COLUMNS(history),
  cc, c+3,
  k, SEQUENCE(r,cc),
  i, INT((k-1)/cc)+1, j, MOD(k-1,cc)+1,
  res, IF(i=1, IF(j>c, INDEX(headers, j-c), INDEX(history, i, j)),
  IF(j=7, INDEX(history,i,5)/INDEX(history,i-1,5)-1,
  IF(j=8, INDEX(history,i,5)-INDEX(history,i-1,5),
  IF(j=9, INDEX(history,i,3)-INDEX(history,i,4),   INDEX(history, i, j )
  )))),
  IFERROR(res,"")
)

Other calculations using INDEX():INDEX() to select ranges from spills, not with reference on cells within it.

 

Please check attached. An error still could appear, I change only couple of spills, you have more plus bunch of other formulas.