Replacing Data Table with a dynamic array

[Youngling]

Up until the arrival of dynamic arrays to Excel I ran Monte Carlo simulations using the DATA TABLE as in range D2:D11 in the example below. Notice that each cell in that range is an independent trial of the computation in D2. Now, if I want to change the number of trials, I have to retype the DATA TABLE formula over a different range, since the range of the DATA TABLE is fixed.

Book4
	A	B	C	D	E	F
1				X + Y	X + Y	X + Y
2	# of Trials	10		-0.6900433	-0.6900433	4.90230132
3	Variable X	0.24648968		0.13805136	-0.6900433	0.24279177
4	Variable Y	-0.936533		-3.2810933	-0.6900433	-1.0600791
5				-0.7484546	-0.6900433	-2.6619196
6				0.65908836	-0.6900433	5.2650448
7				0.17844114	-0.6900433	-0.9840348
8				2.18178978	-0.6900433	-0.2933348
9				-3.7196496	-0.6900433	-0.7381979
10				0.65436751	-0.6900433	1.15480195
11				1.29903028	-0.6900433	1.60067112
Sheet2

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

I am trying to avoid this retyping by using dynamic arrays. The 'solution' in column E does not work because, even though it is a dynamic array, it copies the same trial from D2 in the entire range. It does not 'recalculate' before spilling down the range.

In sum, what I need is to find a way of 'spilling' recalculated values for D2 through some dynamic range of a size determined in a cell like B2.

Your help is kindly appreciated.

PS: I'm aware that I could do something like what's in column F, but I'm then hardcoding the formulas in B3 and B4 into F2, instead of referring to the previously computed sum in D2.

 

[C Moore]

If D3:D11 need to be recalculated, why not use more of them?

scratchbook excel testing.xlsm
	A	B	C
2		# of Trials	10
3		Variable X	Variable Y
4	1	-1.596222	-0.38341434
5	2	0.2987793	-0.29068663
6	3	-0.603611	-0.61886588
7	4	0.8591486	0.111776596
8	5	0.4498857	-0.6676163
9	6	0.3015582	3.566202414
10	7	0.1895971	-0.76526387
11	8	-0.287976	-2.80531789
12	9	0.6103477	-0.04293128
13	10	-1.045259	3.965040829
14	11
15	12
16	13
17	14
18	15
Sheet16

Cell Formulas
Range	Formula
B4:B18	B4	=IF($A4<=$C$2,NORM.S.INV(RAND()),"")
C4:C18	C4	=IF($A4<=$C$2,T.INV(RAND(),2),"")

 

[Youngling]

Thank you for your reply. My example uses two variables (X and Y) for simplicity, but in my real application I have many volatile cells that are ultimately combined into what needs to be simulated. I want to know if I can get away with obtaining many recalculated values of D2 without explicitly having a table on the spreadsheet with the recalculated values of the variables in B4:B23. DATA TABLE precisely accomplishes this, but the range is fixed: It cannot be modified by appealing to a cell like B2. The example below perhaps illustrates this more clearly.

Testing_dynamic_arrays.xlsx
	A	B	C	D	E
1				Sum	Sum'
2	Number of trials	10		209.102611	209.102611
3	Mean(variable)	Variable		208.278184	209.102611
4	1	1.52730914		212.525217	209.102611
5	2	2.39487231		203.986704	209.102611
6	3	3.66527253		208.711987	209.102611
7	4	5.11967139		201.025655	209.102611
8	5	5.68002569		212.821205	209.102611
9	6	4.47093364		218.23053	209.102611
10	7	8.36905325		210.975609	209.102611
11	8	6.30004515		207.029751	209.102611
12	9	10.9174187
13	10	9.26378106
14	11	10.2586394
15	12	11.3752657
16	13	12.227859
17	14	13.5280752
18	15	13.6182408
19	16	16.0834745
20	17	17.1879261
21	18	18.3889668
22	19	19.6737961
23	20	19.051985
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SEQUENCE(B2,,D2,0)
D2	D2	=SUM(B4:B23)
D3:D11	D3	=TABLE(,D14)
B4:B23	B4	=NORM.INV(RAND(),A4,1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

In sum, what I need is to find a way of 'spilling' recalculated values for D2, through some dynamic range of a size determined in a cell like B2. Ideally, using dynamic arrays themselves. If SEQUENCE(B2,,D2,0) recalculated before copying, I'd be set, but, alas, this is not so.

Hope this clarification helps, and Thank You for your kind consideration.