Home

Help to run multiple simulations in one cell - Monte Carlo

%3CLINGO-SUB%20id%3D%22lingo-sub-733596%22%20slang%3D%22en-US%22%3EHelp%20to%20run%20multiple%20simulations%20in%20one%20cell%20-%20Monte%20Carlo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733596%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20run%20simulations%20in%20excel%20and%20find%20the%20min%2C%20max%20and%20median%20of%20the%20results.%3C%2FP%3E%3CP%3EThe%20formula%20used%20in%20each%20cell%20to%20run%20the%20simulation%20is%26nbsp%3B%3D%24B%244*(1%2BNORMINV(RAND()%2C%24B%243%2C%24B%242)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESay%20there%20is%205000%20simulations%2C%20how%20can%20i%20find%20the%20summary%20of%20min%2C%20max%20etc%20without%20using%205000%20rows%20of%20data.%20Happy%20to%20use%20macros%2C%20VBA%2C%20formula%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20and%20suggestions%20are%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20which%20includes%20how%20the%20simulation%20and%20calc%20works%2C%20and%20the%20desired%20output%20summary.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-733596%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749183%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20run%20multiple%20simulations%20in%20one%20cell%20-%20Monte%20Carlo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20has%20long%20had%20a%20little-known%20feature%20called%20%22Data%20Tables%22%20that%20may%20be%20used%20for%20Monte%20Carlo%20simulations.%20I%20used%20the%20a%20one-input%20Data%20Table%20in%20which%20the%20numbers%201%20through%20500%20were%20successively%20input%20into%20Formula%20Example%20worksheet%20cell%20M1.%20Each%20time%20such%20a%20number%20was%20placed%20in%20M1%2C%20the%20formulas%20in%20Sim%20%231%20through%20Sim%20%2310%20recalculated.%20The%20results%20from%20cells%20B7%20to%20B11%20were%20then%20reported%20back%20to%20the%20Data%20Table.%20In%20so%20doing%2C%20you%20tabulate%20the%20results%20of%205000%20simulations%20in%20a%20couple%20of%20seconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Data%20Table%20feature%20may%20be%20found%20in%20Data...What-If%20Analysis...Data%20Table%20menu%20item.%20The%20results%20appear%20in%20Formula%20Example%20columns%20O%3AS.%20Although%20I%20tabulated%20the%20dollars%2C%20you%20could%20just%20as%20easily%20report%20back%20percentages%20or%20any%20other%20values%20from%20what%20I%20like%20to%20call%20the%20%22calculation%20engine%22%20part%20of%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20set%20up%20the%20Data%20Table%2C%20I%20listed%20the%20values%201%20through%20500%20in%20column%20N%2C%20and%20put%20formulas%20in%20cells%20O2%3AS2%20to%20return%20values%20from%20B7%3AB11.%20Those%20cells%20have%20a%20Custom%20number%20format%20%3B%3B%3B%20so%20you%20don't%20see%20any%20values.%20I%20then%20selected%20N2%3AS502%20and%20used%20the%20Data...What-If%20Analysis...Data%20Table%20menu%20item.%20The%20tricky%20part%20is%20to%20specify%20cell%20M1%20as%20the%20%3CEM%3EColumn%20input%3C%2FEM%3Ecell--I%20frequently%20put%20it%20in%20the%20Row%20input%20cell%20by%20mistake%20when%20learning%20how%20to%20set%20up%20Data%20Tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751317%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20run%20multiple%20simulations%20in%20one%20cell%20-%20Monte%20Carlo%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751317%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F23287%22%20target%3D%22_blank%22%3E%40Brad%20Yundt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%20for%20the%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20my%20first%20time%20using%20data%20tables%20so%20i%20will%20explore%20this%20over%20the%20weekend.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
calof1
Contributor

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.

2 Replies

@calof1 

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.

Hi@Brad Yundt 

 

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,