From the course: Excel VBA: Process Modeling

Use the Poisson distribution to calculate arrival times

From the course: Excel VBA: Process Modeling

Start my 1-month free trial

Use the Poisson distribution to calculate arrival times

“

- [Instructor] When you model the amount of time between events, such as customers entering a store, or cars arriving at a toll booth, you use the Poisson distribution from statistics to calculate those enter arrival times. There is no easy way to calculate Poisson distribution results using a single Excel function, but in this movie, I will show you a straightforward way to get the values you need using a series of worksheet formulas. My sample file's chapter 0402, and you can find it in the chapter four folder of your exercise files collection. Over on the left side of the SimSetup worksheet, you'll see that we have the number of stations, and then information about each of the stations. On the right, we have the Poisson data label here, and also, below that we have the label for the mean. The Poisson distribution uses a single input value which is the mean, to calculate its curve. You will also see the Poisson mean referred to as lambda. So let's say that we tend to get a customer every 20 ticks of the clock. That could be five seconds, that could be three seconds, whatever. So I'll click in cell J two, and type 20. Now I need to create formulas for the distribution results in cells M two through M54. To select those cells, I will go to the name box at the top left corner of the worksheet, and type M two colon M54, and press enter, and you can see that those cells have been selected. Cell M two is the active cell, so I'll start typing my formula there. So I'll do equal, Poisson, P-O-I-S-S-O-N, dot DIST, followed by a left parenthesis, L two is the value of zero. So, in other words if a value of zero comes up, then a comma, then J two, which I will make absolute, because I don't want it changing, so I'll press F4, there we go, then a comma, and true, for a cumulative probability distribution, and a right parenthesis. What this formula does, is calculate the probability that a particular value or less will occur. So we're looking at probability of zero, probability of one, and all the way down to whatever value is in cell M54. With this formula, and M two, I can enter it into the rest of the selected cells by pressing ctrl+Enter. And you see we get some very small values at the top so we're almost never going to see a zero, a one, a two, and so on. But if we scroll down, we'll see that if the number is between .066 and .10486, then we will see the number 14 as our result. And it scrolls down, you can see that the average of 20 will occur more frequently than other values, and the likelihood of even larger values decreases as we go toward one. Now that we have our lookup table, and columns L and M, we can create a formula that will generate a random value using that lookup table. So I'll click in cell K two, type equal, and the formula is match left parenthesis, then rand with open and close parentheses, and a comma, the range is M two to M54, those are the cells in our lookup table, and a comma, and one. So we're looking for values that are less than the value in cells M two to M54. Right parenthesis and enter, and we get a value of 21. So in other words, if we scroll down, we'll see that we had a value of less than 0.64369, and greater than .5, basically .56. If I scroll back up, and go to the formulas tab, and recalculate the worksheet, then we get 23, recalculate again, 31, that's an unusually high value, one more time, 19, which is only slightly below average. So as you can see, every time we recalculate the worksheet, we get a new arrival time based on the Poisson distribution with a mean of 20.

Contents