# Replacing Data Table with a dynamic array

#### Youngling

##### New Member
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
ABCDEF
1X + YX + YX + Y
2# of Trials10-0.6900433-0.69004334.90230132
3Variable X0.246489680.13805136-0.69004330.24279177
4Variable Y-0.936533-3.2810933-0.6900433-1.0600791
5-0.7484546-0.6900433-2.6619196
60.65908836-0.69004335.2650448
70.17844114-0.6900433-0.9840348
82.18178978-0.6900433-0.2933348
9-3.7196496-0.6900433-0.7381979
100.65436751-0.69004331.15480195
111.29903028-0.69004331.60067112
Sheet2
Cell Formulas
RangeFormula
E2:E11E2=SEQUENCE(B2,,D2,0)
F2:F11F2=NORM.S.INV(RANDARRAY(B2))+T.INV(RANDARRAY(B2),2)
B3B3=NORM.S.INV(RAND())
B4B4=T.INV(RAND(),2)
D2D2=B3+B4
D3:D11D3=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.

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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### C Moore

##### Well-known Member
If D3:D11 need to be recalculated, why not use more of them?
Cell Formulas
RangeFormula
B4:B18B4=IF(\$A4<=\$C\$2,NORM.S.INV(RAND()),"")
C4:C18C4=IF(\$A4<=\$C\$2,T.INV(RAND(),2),"")

#### Youngling

##### New Member
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
ABCDE
1SumSum'
2Number of trials10209.102611209.102611
3Mean(variable)Variable208.278184209.102611
411.52730914212.525217209.102611
522.39487231203.986704209.102611
633.66527253208.711987209.102611
745.11967139201.025655209.102611
855.68002569212.821205209.102611
964.47093364218.23053209.102611
1078.36905325210.975609209.102611
1186.30004515207.029751209.102611
12910.9174187
13109.26378106
141110.2586394
151211.3752657
161312.227859
171413.5280752
181513.6182408
191616.0834745
201717.1879261
211818.3889668
221919.6737961
232019.051985
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=SEQUENCE(B2,,D2,0)
D2D2=SUM(B4:B23)
D3:D11D3=TABLE(,D14)
B4:B23B4=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.

Replies
0
Views
148
Replies
2
Views
82
Replies
4
Views
2K
Replies
3
Views
72
Replies
2
Views
104

1,148,158
Messages
5,745,108
Members
423,924
Latest member
Gazzat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back