'Can you reproduce this strange #SPILL! ? Can you explain it?

In cell B3 I put this RANDARRAY :

=RANDARRAY(3,5,1,20,1)

to test a small LET to generate UNIQUE values across an array:

=LET( array,B3#,
       aCols, COLUMNS( array ),
       eSeq, SEQUENCE( ROWS( array ) * aCols,, 0 ),
       UNIQUE( INDEX( array, eSeq/aCols + 1, MOD( eSeq, aCols ) + 1 ) ) )

that I put in H3.

When it calcs, it spills a list of unique values from the array (whereas UNIQUE generates an either row-wise or column-wise).

All good, but I notice that when I calc (F9), it occasionally returns #SPILL!. (and there was nothing in the rows underneath that would force a #SPILL!(

So, I trapped when it does this and copied the values to a new range and ran the formula on just those values. The #SPILL! does not happen.

Then I put the formula =B3 into J3 and copied it across to make a new array of cells that simply looks at each cell in the B3# array and I made a copy of my LET formula and placed it in P3 with array set to J3:N5. Then I hit F9 until the #SPILL! recurred.

It happens quite frequently - about 15% of the time.

Usually, the #SPILL! happens on both arrays (B3# and J3:N5), but every so often it happens to only one of the two copies:

Happens to J3:N5

enter image description here

Happens to B3#

enter image description here

Most of the time, it happens to both:

enter image description here

Any idea why?

Can anyone reproduce this?

Source of the problem, but no explanation

P.b. proposed in the comments this (much better) formula. It exposed that the root of the problem comes from UNIQUE in combination with a Random input. So I have now tried =UNIQUE(RANDARRAY(15,1,1,20,1)) and found that it has the #SPILL! problem.

What is also interesting is that this formula can be placed in multiple cells and all of them tend to #SPILL! simultaneously even though they would have independent inputs. These result in #SPILL! about 95% of the time if more than one is in a workbook.

Here I have a dozen of them - only one succeeded and that was after hitting F9 about 10 times.

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source