Join Curt Frye for an in-depth discussion in this video Using the exponential distribution, part of Data-Analysis Fundamentals with Excel.
- View Offline
- One of the most important pieces of information you can discover about your business is how often a given event tends to occur. For example, if you sell products via your company's website, knowing the average time between orders helps you plan the number of employees you'll need to have on duty at any time. This type of occurrence is described by the exponential probability distribution. You need to know three pieces of information to calculate a probability using the exponential distribution: the average time, the time you want to check, and whether you want to have a probability at that specific time, or any time up to that time.
So, for example, you could find the probability for the time between orders being 10 minutes, or anything up to 10 minutes. I've set up my worksheet with a table here on the left, with minutes from 1 to 25, if I just scroll down using a scroll wheel, you can see that. A column to put the probability in, and then also my average of 10 minutes. I'll click in cell B2 to create my first formula. What I'm doing is creating a formula that will calculate the probability of one minute occurring between orders.
Then I'll copy that down to the rest of the cells in that column. So, with B2 selected, I'll type an equal sign, and I'll type "expon", E-X-P-O-N, "dist". So I will use the bottom function here. You can see from the tool tip EXPONDIST is a function that was available in 2007 and earlier, and it's in 2010 and later editions for compatibility. EXPONDIST and EXPON.DIST are the same function.
It's just that in Excel 2010 the Excel programming team updated the name of a number of the functions, and that's the new name that fit the new naming scheme that they created, but they're the same function. I will press Tab. Now I need to enter in my three arguments. The first is the x, and that is 1 minute, which is in cell A2, and then a comma, and now the lambda. Lambda is 1 over your average.
I'll type in 1, then a forward slash for division, then a reference to cell E1, which is where my average is located. I don't want that cell reference changing when I copy the formula, so I'll press F4, to make it an absolute reference, then I'll type a comma, and now I need to indicate whether I want the probability of any time up to one minute, or two minutes, or three minutes as I copy it down, or the probability of that specific time occurring.
In this case, I want the specific time, which is the probability density function, so I will highlight FALSE, press Tab, check my formula, everything looks good, type right parenthesis, and press enter. So there I see a probability of about 9% for one minute. Now I'll click cell B2, hover the mouse pointer over the fill handle at the bottom right corner of the cell, and it drag down until I cover the cell B26, next to 25 minutes, release, and there I get my values.
You can see from the chart how the distribution will work. You'll see a lot of waits around one or two minutes, and the probability becomes less and less, as we go out toward 25 minutes. If you want to display or calculate the cumulative function, that is the probability that a particular time will be equal to or less than the time here in cells A2 through A26, you can change to the cumulative probability distribution.
If I click cell B2 to edit the formula, and change the last argument from FALSE to TRUE, and press enter, I get the same value as I had before, because equal to or less than 1 doesn't change anything in this data. But now, when I drag down to cover cell B26, you can see that the curve has changed, and I have a low likelihood of any value say equal to or less than 5, which is one half of the average, and then the curve slowly trends upward.
Instead of the probability getting less and less as you go along, it actually increases until you get very close to 1. If I were to extend out another number of values, that curve would get even closer to 1, but of course, never actually touch it. If you want to calculate the probability that a wait will be between two times, say between eight and 12 minutes, then all you need to do is subtract the probability of the lower time, from the probability of the higher time, and we're using the cumulative probability for this calculation.
If I want to calculate the probability that the wait between orders will be between eight and 12 minutes, all I need to do is subtract the value in cell B9 from the value in cell B13. So I'll click cell E3, type equal, and I have B13, which is the wait for 12 minutes, minus B9, which is the wait for eight minutes, press enter, and I get a result of 14.81% Calculating the times between events helps you forcast your company's needs.
Using Excel's EXPONDIST function helps you calculate those probabilities quickly.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Calculating mean and median values
- Analyzing data using variance and standard deviation
- Minimizing errors
- Visualizing data with histograms, charts, and more
- Testing hypotheses
- Measuring covariance and correlation
- Performing Bayesian analysis