 Statistics and Excel. Poisson Distribution. Potholes in Road Example. 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'll basically build this from a blank worksheet. If you do have access, three tabs down below. Example, practice blank. Example, in essence, answer key, practice tab, having pre-formatted cells so you can get to the heart of the practice problem. Blank tab, basically a blank worksheet except for our image here, which you don't really need for the practice problem. So we can practice formatting the cells within Excel as we work through the practice problem. Let's go to the example tab to get an idea of where we will be going, looking at the Poisson Distribution. Once again, oftentimes examples related to line-rating situations as we saw in a prior presentation. But we can also have situations over space. So in this case, we're talking about potholes in a road. Thinking about the mean number of potholes, the average number of potholes being 20 for 100 miles of road. As opposed to a line-weighting situation where we were saying how many people will show up over a certain interval of time. Let's go back to the blank tab now and populate some of our data up top. So I'm going to go into A1. Let's first format the entire worksheet like we normally do, select in the triangle, right-click it on the selected area. We're going to format the cells. I'm going to go to currency, negative numbers, bracketed and red, no dollar sign. I'm going to get rid of the decimals for now and add them as I need them. Okay, let's go to the home tab, font group and make the whole thing bold as well. You don't have to do that, but I will do that. Alright, so here's our conditions. We're going to say that the mean potholes per 100 miles per, I'm going to say per miles, so that I can put them in a separate cell. I'm going to make A a little bit larger. So the mean number of potholes I'm going to say is 20 per 100 miles. Okay, so now what I would like to do is kind of imagine as we did last time that we were actually going out and counting the potholes per 100 miles. Now, in this case, to do that, to get a random generated number, we have to already have the mean, but in practice you might not know the mean, right? What would we do to start this process? We might go out and count the potholes for every 100 mile interval and try to see how many potholes are in each of those 100 mile intervals. And then with our data, we might plot our data to then see if it conforms with a Poisson distribution. And if it does, the Poisson distribution might be a way for us to more easily make predictions and predictions into the future about the pothole situation. So let's do a random number generation which will mirror or mimic us basically going out and counting the potholes. To do that, we're going to go into the data tab and we need the data analysis. We saw turning that on last time. If you don't have that, you go to the file tab. You go to the options down below and then you go to the add-ins and then manage. You want the Excel add-ins and go and then you want the analysis tool kit. That's the one we want. Okay, so I'm going to make A a little bit wider here. I'm going to make my image a little bit smaller. I'm going to make column C a little bit smaller. I'm going to put the data here. So this is going to be my X data results. Let's format that first home tab font group. I'm going to make it black and white, which is our normal heading formatting. Let's center it as well. And then I'm going to go into D2 because that's where I want the data to go. Insert, I'm sorry, not insert, data up top. And then we want to go into the analysis and the data analysis. I'm going to do a random number generation and here's the criteria. I'm going to put a 1 here because we just want one column, number of random numbers. Let's put a thousand again. So we're just going to generate a thousand numbers that we'll be doing. Those thousand numbers representing a thousand 100 mile tests that we did. So that might be a fairly large test. Let's put like 500, like 500, 100 mile tests. Let's do that one. And then it's not going to be a discrete. We wanted to give it to us in accordance with a Poisson distribution, which still has an element of randomness, but it's going to be distributed according to a Poisson distribution with the lambda, the mean being, we're going to say 20. Now again, in real life, we might not know the mean. We'd go out and count potholes to basically figure this out, right? We would be counting every 100 miles, the number of potholes within each 100 mile frame. Let's go ahead and say, oh, where do I want it? The output? I want it to go right there. So that's where the output is going to go. Okay, let's go. Okay, and see what we get. So what's this mean then? It means that for the first 100 miles that we looked at, there were 18 potholes. Then there was 26 potholes in the second 100 mile space. The third 100 mile space, 21 potholes, 26 potholes in the next 100 mile space, and so on and so forth. So now we can take this data and we can create our bins with it to see if we can see any patterns with that data and possibly if it corresponds to closely or relatively closely to some kind of distribution that we can make a function for like the Poisson distribution. So let's say that we have our bins are going to be anywhere from, we're going to say, let's go from, I'll say 0, 1, 2, and I'm going to bring it all the way up to 100 here. So I'm going to select those three numbers, fill handle, drag it down to 100. Remember that in theory, there can be like an infinite number of potholes. The whole road could be a pothole, I guess, in theory, right? Because there's no upper limit if we're talking about a Poisson type distribution. But if the mean is 20, then you would expect you're not going to get, you know, it's going to be quite rare that you're going to have potholes more than like 100 potholes in that interval given if it's in a Poisson distribution format. Now remember if we want the frequency, frequency, we could try to say, I want to count these numbers, and I could try to use a count if function equals count if brackets, and the criteria range would be this. I'm going to hold down control shift down arrow, getting down to the bottom, control backspace going back to the top, and then comma, count that if the criteria is zero. And I could say enter and copy that down. Now I'm not going to leave this as the way we do it, because the other way is a spill function for the frequency. I just want to show that sometimes this doesn't give us the result that we would expect, right? So if I sum this up at the bottom by saying alt equals, I should get to 500 and I don't. And that's because sometimes I think that the distribution might give us a number that's not exactly