0

In cell A1 i have the following: =TOCOL(E1:G6,3) In Cell B1 i have the following =UNIQUE(VSTACK(E1:E6,F1:F6,G1:G6)) In cells E1 to G6 I have this formula =LET(RandNum,RANDBETWEEN(1,100),IF(RandNum>90,NA(),RandNum)) There is nothing else on this worksheet. I press F9 to recalculate and every now and again (with no apparent pattern) Cells A1 and B1 show #SPILL! errors. Is there a valid reason for these #SPILL! errors or is this a bug in excel?

It appears to be as a result of the varying result range rather than anything being present in the spill range. If you copy and paste the range as values and re-calculate then A1 and B1 calculate just fine. It appears to be some sort of issue with the varying result range and the way spill formulas are calculated behind the scenes. It can sometimes take a while for this error to come up. It may come up more frequently if I change >90 to >50 so the variation in the size of the spill range is greater.

Andy Robertson
  • 171
  • 2
  • 6
  • My understanding is that the #SPILL! error should only occur when something is in the spill range. https://support.microsoft.com/en-us/office/-spill-error-spill-range-isn-t-blank-182c7141-7ff5-4dc2-ba38-7a81b7bf51c7 – Andy Robertson Feb 07 '23 at 18:39
  • 1
    See: https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023 see the part of indeterminate size. – Scott Craner Feb 07 '23 at 18:40
  • Thanks, it still seems odd that it functions correctly most of the time but errors intermittently. – Andy Robertson Feb 07 '23 at 18:42
  • 1
    It is a known issue, not sure if Microsoft has plans to fix or not. – Scott Craner Feb 07 '23 at 18:47
  • Curious now as to how to resolve this question? Should i remove the question or would you or I answer it with reference to the MS explanation - that spill ranges cannot reliably cope with volatilty? – Andy Robertson Feb 07 '23 at 18:52
  • I believe there is duplicate out there, I just do not have time to hunt for it. – Scott Craner Feb 07 '23 at 18:53
  • 1
    Feel free to self answer, just in case no one can find the dup. – Scott Craner Feb 07 '23 at 18:55

1 Answers1

1

According to this support article...

https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023

"Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!."

https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023

Thanks to Scott Craner for the useful comments. Hope this helps someone else.

Andy Robertson
  • 171
  • 2
  • 6