Jul 31 2021 03:12 PM
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?
Aug 01 2021 01:39 AM
Hope this information could help you.
How to troubleshoot "available resources" errors in Excel
Excel cannot complete this task with available resources" error occurs in Excel
Wish you a nice day.
Nikolino
I know I don't know anything (Socrates)
Aug 01 2021 01:12 PM
I opened it in Excel Desktop and Excel for web. In both cases it works correctly.
Aug 06 2021 06:36 PM
@Sergei BaklanStill nothing helped.
Aug 07 2021 03:46 AM
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.