 Statistics and Excel Poisson Distribution Roller Coaster Line Example Part Number Two. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing Excel. Here we are in Excel. If you don't have access to this workbook, that's okay because we basically built this from a blank worksheet. However, we started in a prior presentation, so you could go back there, start with a blank worksheet, you'd probably be okay to start at this point with a blank worksheet as well. If you do have access to this workbook, three tabs down below, example, practice blank, example, in essence, answer key, practice tab, having pre-formatted cells so you could get right to the heart of the practice problem. Blank tab is where we started with a blank worksheet and now we're continuing with it at this point in time. Quick recap of the scenario. We're looking at a Poisson distribution situation which often has to do with line waiting situations. As it does here, lines related to a roller coaster ride in this case. Last time, we imagined that we wanted to figure out how many people arrive in general during like a busy time, for example, in each one-minute time period. So we went out with our stopwatch and we looked at 1000 one-minute time intervals and we counted how many people arrive in each of those one-minute time intervals. So for example, in one one-minute time interval, we had four and then in the next one-minute time interval, three people arrived and the next one-minute time interval, two people arrived and so on and so forth. And we imagined doing that a thousand times. Obviously, we didn't do that in practice a thousand times but instead used the random generator function in Excel, which is in the data tab, analytics group. If you don't have that analytics group, you can add it in the file options that we looked at in a prior presentation. So this simulates us actually kind of gathering the data, which might be useful then to then think about does it conform as many line situations do to a Poisson type distribution. So we then said, okay, let me count up using our buckets and bins and my frequency formula, how many we have counted in our 1000 intervals that had one person arrive versus zero versus one versus two versus three and so on. And then we can look at the percents. So in all of our one-minute time frames, 6.2% of the time zero people arrived, 17.10% of the time one person arrived and so on and so forth. So now that we had our data, we can then plot it and say, if I plot my data, does it look like possibly a Poisson distribution? That would be one indication. Does it conform to the conditions of a Poisson distribution, which we talked about in a prior presentation? Many line situations do. That would be another indication that we might be able to use a Poisson distribution. And oftentimes the variance will be close to the mean if we have a Poisson type of relationship, which we have a pretty close relationship here. So now we're thinking, okay, then I might be able to predict what's going to happen in the future with regards to line waiting situations by instead of trying to extract the actual data using a smooth curve or function, the Poisson distribution. That's what we're going to do now. So if I go to the right and we say, all right, what if I just plotted this with a smooth curve? I started on cell two. So I'm just going to delete cell one. And hopefully that doesn't delete anything or row one. I'm going to put my cursor on row one, right click and delete. And so to bring everything up to the top, because I started on the wrong column, hopefully that doesn't confuse anything too much. We're in column Y. Why? Because that's just how it happened. That's just how things happened. So once again, I'm just going to say equals and bring over my same data from the left. So I'm going to say equals this amount. So I can just bring in over that same data. And then when I calculate the mean, I'm going to say that the mean now equals and notice the original mean that I used to calculate the data set was 2.75. When I actually look at the data, I calculated the data, it came out to a mean of 2.73. So if we were actually looking at our data, we would be using the 2.73, you know, as the mean, because that's the mean of our data. So let's just pick that one up. I'll say the mean is 2.73. That's what's in our data set. I'm going to add some decimals, home tab, number group, add a couple decimals. Let's make column Y a little bit longer. Why? So long why? All right, so then we're going to say, all right, now let's do our data in accordance with a Poisson distribution. Let's just build a table, build a graph and see how it compares to the actual data that we have. So this is the ideal data curve according to a Poisson distribution. We'll call it P of X Poisson function. Let's just call it P of S X for the Poisson. And I'm going to say then let's copy these two and go to the home tab, font group, black, white, let's center it. And there we have it. And then I'm just going to take the X's down to what did we say 29. Remember that the Poisson distribution, I mean, it could go on forever. That's why it's skewed to the right or positively skewed because you could imagine in one minute interval, infinite people arrive, but that's not likely to happen. So we're just going to cap it at 29. So I'm going to say one, two, select those two, put my cursor on the fill handle, drag it down to 29. 29. Bam. All right. And then I'm just going to do my Poisson dot dist function, putting my cursor in AC two equals Poisson dot dist. And so we need the X value, I'm going to pick up this X value, comma, we need the mean, I'm going to pick up the mean here from our data set. I want that to be the same as I copy it down. I'm going to select f four. Notice you could use spills and arrays. But I think in this case, I kind of like using the absolute values, because if I wanted to put a table around or something, I would rather do that typically. So I will switch back and forth from arrays in the areas that I think are most useful for the arrays versus not arrays. And then I'm going to say that we could have cumulative will give us the cumulative down if I put true or I can put false for the probability mass of one, we want the probability mass of one because we're looking at each outcome here. So and I believe that is a zero. So I could put zero or false for probability mass function. That's what we want. Close it up, enter, and then I can put my cursor on it home tab number group percentify it at a