Building Probability Models in Excel Part 4: Discrete Random Variables

Posted on
6,608 Points
Last Modified:
Experience Level: Beginner
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilities and outcomes with the =STDEVPR command.

Video Steps

1. Discrete Random Variables: Type in the set of outcomes and corresponding probabilities in cells A2:B6

2. Enter =SUM(B2:B6) into cell B7

3. Enter =DISCRINV(RAND(),A2:A6,B2:B6) into cell B9 and label “Profit”

4. Enter =B9 into cell B14

5. Select cells A14:B213

6. Click Tools > SimTools > Simulation Table

7. Enter =AVERAGE(B14:B213) into cell B11 and label “Mean”

8. Enter =STDEV(B14:B213) into cell B12 and label “StDev”

9. Enter =STDEVPR(A2:A6,B2:B6) into cell C12

Author:Toby Reaper
Often times what you may consider better indicators of various items, others do not.  It is always a good idea to have a way to turn off conditional formatting in whatever program is displaying it.  The most obvious I will use here - MS Excel.
The Data Visualizer (DV) add-in for Excel provides a powerful, fast way to build flowcharts and swimlane diagrams from data. And you don’t even need a Visio license to make this magic happen!