using SEQUENCE to spill the content of a single cell over a range

[Youngling]

Setting
Suppose I want to have the numbers from 1 to 10 generated in the range B1:B10. I can store N in A1 and type =SEQUENCE(A1) in B1. A dynamic array is generated that gets the job done.

My problem
I have an arbitrary computation in A2 that I want to copy 10 times in the range C1:C10. I want to be able to use dynamic arrays somehow, in such a way that: (a) I type some formula in C1, (b) this formula reads the "10" in A1, and the content of A2 is automatically copied in the range C1:C10. I want the solution to be so that, if I change A1 to, say, 20, the range where A2 is 'spilled' into is automatically adjusted to be C1:C20.

I do not want to use VBA to solve the problem.

I'm using Microsoft 365 (Excel 16.46.21021202) on a MacBook Pro (2019) running Catalina (10.15.7).

Any help would be greatly appreciated.

 

[kweaver]

Is this what you mean?

Book1 (version 1).xlsb
	A	B	C
1	20		100
2	100		101
3			102
4			103
5			104
6			105
7			106
8			107
9			108
10			109
11			110
12			111
13			112
14			113
15			114
16			115
17			116
18			117
19			118
20			119
Sheet3

Cell Formulas
Range	Formula
C1:C20	C1	=SEQUENCE(A1,1,A2)
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Maybe one of these

+Fluff 1.xlsm
	A	B	C	D
1	10	1	1	100
2	100	2	4	100
3		3	9	100
4		4	16	100
5		5	25	100
6		6	36	100
7		7	49	100
8		8	64	100
9		9	81	100
10		10	100	100
11
Stock

Cell Formulas
Range	Formula
B1:B10	B1	=SEQUENCE(A1)
C1:C10	C1	=B1#^2
D1:D10	D1	=IF(B1#,A2)
A2	A2	=A1^2
Dynamic array formulas.

 

[Youngling]

No, but your answer gave me the idea to get what I first wanted: =SEQUENCE(A1,,A2,0). Very grateful, thank you.

Which brings me to my next question: suppose that A2 contains the formula = A1 * RAND(). Then using my solution above =SEQUENCE(A1,,A2,0) yields a dynamic array of constant numbers. If I hit 'recalculate' the numbers are updated but they're all the same. My hope was that every time the number got 'copied' down, that the machine would recalculate, so that I would end up getting . This is, for example, what would happen if I used the DATA TABLE array formula {=TABLE(,A3)} in C2:C10. In a sense, I am hoping to replace that DATA TABLE command with something that was dynamic.

 

[Fluff]

I've never used Data tables so have no idea what you are trying to do, but the reason all your values are the same is that you have used a step of 0 in the formula.

 

[Eric W]

Have you looked at the RANDARRAY function?

RANDARRAY function - Microsoft Support

Learn about the RANDARRAY function for creating arrays of random numbers between 0 and 1. RANDARRAY is in a class of functions called dynamic arrays.

support.microsoft.com

 

[Youngling]

The following mini may clarify matters further. I want 10 'copies' of what is in B6. If I use =SEQUENCE(B2,,B6,0) in cell D2 I get my ten copies (in range D2 to D11). But they are 'the same.' If I refer to B6 in E2 and then use {=TABLE(,C1)} in cells E3:E11 I get my ten 'independent and identically distributed' copies, using the data table array formula, which is what I want, but then if I change the number of copies to, say, 20, in B2, i'd have to reprogram the data table. I'm trying to accomplish what the data table does in this example with dynamic arrays so that the size of the array is dynamic.

Book4
	A	B	C	D	E
1				X + Y	X + Y
2	Trials	10		-0.3473319	-0.3473319
3	Variable X	-0.571409186		-0.3473319	-0.1615321
4	Variable Y	0.224077303		-0.3473319	-1.2400353
5				-0.3473319	-1.3728739
6	X + Y	-0.347331883		-0.3473319	-0.1272208
7				-0.3473319	3.44566243
8				-0.3473319	-3.473457
9				-0.3473319	0.23469291
10				-0.3473319	0.60382376
11				-0.3473319	-2.8717248
Sheet2

Cell Formulas
Range	Formula
D2:D11	D2	=SEQUENCE(B2,,B6,0)
B3	B3	=NORM.S.INV(RAND())
B4	B4	=T.INV(RAND(),2)
B6	B6	=SUM(B3:B4)
E2	E2	=B3+B4
E3:E11	E3	=TABLE(,C1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[Youngling]

Have you looked at the RANDARRAY function?

RANDARRAY function - Microsoft Support

Learn about the RANDARRAY function for creating arrays of random numbers between 0 and 1. RANDARRAY is in a class of functions called dynamic arrays.

support.microsoft.com

Yes, I have looked at RANDARRAY. Thank you. It does what I need if I can 'bake' all my computations in cell D2 (in my example above I would type =NORM.S.INV(RANDARRAY(B2))+T.INV(RANDARRAY(B2),2) in cell D2), but sometimes that is unwieldy, in such a way that I want to be able to simply refer to cells that then get recalculated, as in my data table example.

 

[Youngling]

Since my original questions was answered I will close this thread and ask my updated question in a new post. Thank you everybody!!