Jul 01 2019 11:32 PM
Hi Everyone,
I am looking to run simulations in excel and find the min, max and median of the results.
The formula used in each cell to run the simulation is =$B$4*(1+NORMINV(RAND(),$B$3,$B$2)).
Say there is 5000 simulations, how can i find the summary of min, max etc without using 5000 rows of data. Happy to use macros, VBA, formula etc.
Any help and suggestions are much appreciated.
I have attached an example which includes how the simulation and calc works, and the desired output summary.
Jul 10 2019 08:09 PM
Excel has long had a little-known feature called "Data Tables" that may be used for Monte Carlo simulations. I used the a one-input Data Table in which the numbers 1 through 500 were successively input into Formula Example worksheet cell M1. Each time such a number was placed in M1, the formulas in Sim #1 through Sim #10 recalculated. The results from cells B7 to B11 were then reported back to the Data Table. In so doing, you tabulate the results of 5000 simulations in a couple of seconds.
The Data Table feature may be found in Data...What-If Analysis...Data Table menu item. The results appear in Formula Example columns O:S. Although I tabulated the dollars, you could just as easily report back percentages or any other values from what I like to call the "calculation engine" part of the worksheet.
To set up the Data Table, I listed the values 1 through 500 in column N, and put formulas in cells O2:S2 to return values from B7:B11. Those cells have a Custom number format ;;; so you don't see any values. I then selected N2:S502 and used the Data...What-If Analysis...Data Table menu item. The tricky part is to specify cell M1 as the Column input cell--I frequently put it in the Row input cell by mistake when learning how to set up Data Tables.
Jul 11 2019 04:32 PM
Thank you kindly for the assistance.
Its my first time using data tables so i will explore this over the weekend.
Thanks again for your help.
Kind regards,
Feb 27 2023 08:58 AM - edited Feb 28 2023 02:25 AM
@calof1 I appreciate I'm very late to the party but i happened upon your problem while learning about Lambda and found it such a a neat solution. by making a recursive LAMBDA function to simulate each scenario, and MAKEARRAY to compile a set of scenarios, it was simple enough to get the entire simulation into an array. i then used LET to make that array sticky while computing the stats on it then a further MAKEARRAY just to spill the results onto the screen.
=LAMBDA(seed,sd,nominal,years,iter,
LET(iter,iter+1,
seed, seed*(1+NORMINV(RAND(),nominal,sd)),
IF(iter=years,seed,simulatenorminv(seed,sd,nominal,years,iter)
)
)
)
=LET(
sims,
MAKEARRAY(P6,1,
LAMBDA(row,column,
simulatenorminv(P4,P2,P3,P5,0)
)
),
MAKEARRAY(5,1,
LAMBDA(row,column,
SWITCH(row,
1,AVERAGE(sims),
2,MEDIAN(sims),
3,STDEV(sims),
4,MIN(sims),
5,MAX(sims)
)
)
)
)