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