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,

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies