How to plot continuous probability distributions (in Excel)
Task
There are many famous continuous probability distributions, such as the normal and exponential distributions. How can we get access to them in software, to plot the distribution as a curve?
Related tasks:
- How to generate random values from a distribution
- How to compute probabilities from a distribution
- How to plot discrete probability distributions
Solution
We begin by creating the values that will be shown on the $x$-axis. These values depend not only on the distribution, but on which portion of it you wish to see.
For example, for an exponential distribution, the sample space is $(0,\infty)$, but for a standard normal distribution, it is the whole real line, and you may wish to view just $( - 5,5)$, or some other range.
In the example below, we will use a Gamma distribution with $\alpha = 5$ and $\beta = 5$, plotted on the range $\lbrack 0,50\rbrack$, but the particular example doesn’t matter; you can use the procedure below for any distribution.
To generate the $x$ values from 0 to 50, begin with just the first two values in the sequence, in this case 0 and 1, as shown below.
Drag the small green square in the bottom right of the selection downward, to create a sequence that goes all the way up to 50. (Only the beginning of it is shown here.)
If your sample spaces were a smaller range (say, just from -2 to 2), you would need to use smaller steps to get a smooth plot. For example, you might begin with -2 and -1.9 to tell Excel to take steps of size 0.1.
In the adjacent column, we put the formula for the distribution, based on the $x$ values in the first column. In this example, recall that we’ll plot a Gamma distribution with $\alpha = 5$ and $\beta = 5$, so we use the formula shown below. The final parameter for the distribution should always be FALSE, to indicate that we are not asking Excel for a cumulative distribution function, but just the usual probability density function.
After typing your probability density function’s formula, drag it down the column.
Highlight just column B and insert a line chart from the Insert tab on the Ribbon, as shown below.
This will create a chart that does not yet include your desired $x$-axis labels; rather, the horizontal axis markings will be 1, 2, 3, 4, etc. To get the correct labels on the $x$-axis, right-click the chart and choose “Select Data…” This will bring up the window shown below.
Click the Edit button for the Horizontal (Category) Axis Labels and select column A. Click OK twice to return to your plot, which should then have the correct $x$-axis labels. You can then update the chart title and axis labels to be more descriptive if desired, as shown in the final result, below.
Although we used the GAMMA.DIST function in Excel, you can use any of the built-in continuous probability distribution functions, such as BETA.DIST, CHISQ.DIST, F.DIST, NORM.DIST, LOGNORM.DIST, or T.DIST.
Content last modified on 24 July 2023.
See a problem? Tell us or edit the source.