SEQUENCE & RANDBETWEEN and the Mystery of #SPILL! Errors

[jase71ds]

I'm trying to understand what causes this #SPILL! error.

On a new workbook and a clean worksheet:
A1 = SEQUENCE(RANDBETWEEN(1,10))

Now, repeatedly toggle F9 (refresh) and observe the results.

Most of the time you'll get a shrinking or growing column of numbers, but often enough (about 30% of the time) you'll get a #SPILL! error.

Can someone explain to me what causes the #SPILL! error?
I can't discern a pattern.

Addendum #1.
To add to the mystery...

Now copy this formula to B1, and you'll get less results and a higher frequency of #SPILL! errors.

Now copy it all the way to Z1, and you'll hardly ever get A1 to give you results.

Summary: The more times the formula appears in the worksheet (or the workbook as a whole) the greater the frequency of #SPILL! errors.

At least, that is my experience.

What gives!!???

 

[Eric W]

Very weird. You can move the RANDBETWEEN out of the SEQUENCE, and it still does that. I tried RAND() instead of RANDBETWEEN and FILTER instead of SEQUENCE, and it still does it.

Book1
	D	E	F	G	H
1	1	a		4	#SPILL!
2	2	b
3	3	c
4	4	d
5	5	e
6	6	f
7	7	g
8	8	h
9	9	i
10	10	j
Sheet9

Cell Formulas
Range	Formula
G1	G1	=INT(RAND()*10)+1
H1	H1	=FILTER(E1:E10,D1:D10<=G1)

I wondered if it has something to do with volatile functions, so I tried using NOW() like this for the G1 formula:

=MOD(SECOND(NOW()),10)+1

but that did not cause the problem. It's almost like the formula is trying to SPILL onto the previous version of itself. That doesn't really make sense though. Something to do with the internal calculation chain I'd guess.

 

[Fluff]

Definitely odd, I also tried using RANDARRAY to return just one number in another cell & got the same thing , but if I put the RANDARRAY in the SEQUENCE function, I only ever got 1 row

+Fluff 1.xlsm
	A	B	C	D
1	1	#SPILL!	1	2
2			2
3			3
4			4
5			5
Main

Cell Formulas
Range	Formula
A1	A1	=SEQUENCE(RANDARRAY(1,,1,10,1))
B1	B1	=SEQUENCE(D1)
C1:C5	C1	=SEQUENCE(RANDBETWEEN(1,10))
D1	D1	=RANDARRAY(1,,1,10,1)
Dynamic array formulas.

 

[jase71ds]

Good point about pulling RANDBETWEEN out of the SEQUENCE function.
I put RANDBETWEEN in A1, and then A2 through Z2 put =SEQUENCE($A$1)
Now they are all feeding off the same random number - some break, some don't. Completely inconsistent.

Definitely some sort of internal calculation issue / bug.