Replacing Data Table with a dynamic array

Youngling

New Member
Joined
Mar 11, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
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.

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.
 

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
Joined
Jan 17, 2014
Messages
585
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 11, 2021
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top