 Statistics and Excel Poisson Distribution Potholes in Road, 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 to that presentation, start with a blank worksheet, or you'd probably be okay starting from this point going forward with a blank worksheet. 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 are continuing on with it at this point in time. Quick recap of what we did in a prior presentation, looking at a Poisson distribution situation, but this time instead of a line waiting situation, instead of going over time intervals, we're going in a pothole situation, looking at space intervals, space in the roads in this case. So we started out imagining that we went out and actually counted how many potholes were in the road for every 100-mile time span, and we generated our data here using a random number generator, which can be found in the data and the data analysis, but it's not just simply random, it's random in accordance with the Poisson distribution and the mean being 20. In real life, we wouldn't really know the mean would be 20, but we would be counting the potholes and then possibly analyzing the data. When we then did analyze the data, we then grouped the data together and said, okay, how many times in 100 miles were there 12 potholes and there were four of them? We did 500-mile tests, right? How many times were there 13 potholes in our 500 tests? There were 17 of them. And then based on this data, we created our graph over here. We also took a look at the percent of each to the total, which can have a graph, similar graph, this way. We then calculated the mean of our data sets. So the average number of potholes was 20.14. That's pretty close to the variance, which gives us an indication that it might be a Poisson distribution. So now let's do an actual Poisson distribution, which will be a more exact curve now. So now we're going to say, let's see how close it lines up to an exact Poisson curve. I'm going to say this equals the mean. I'm just going to pick up the same mean, and I'm going to copy that down, putting my cursor on it, copy it down per miles. I'm just copying my data over so we can use that same data to be able to see it here. So I'm going to say this equals the 20 and this equals the 100, so that we just have that same data over here, but it's tying into our other data set. So if I change that other data set, this data set will change automatically. So now let's do our X number of potholes. So X, by the way, X is equal to number of potholes in the 100 mile and 100 mile span, let's say. And so I'm going to make, let's make W a little bit smaller. I'm going to control scroll in a bit. All right. And so then we're going to say this is going to be p of X, which is going to be the Poisson. In this case, we're going to say what's the likelihood that we have that number of potholes. I'm going to make this a header format by going to the home tab font group black white center, and that should do it. And then I'm going to go from 012. I'm going to bring it up to 100 again, 0 potholes, 1 pothole, 2 potholes, select in those three, put in my cursor on the fill handle, bringing it down to 100. Now it could go up to like infinity in theory, but in practice, you would think that if you had 100 potholes in 100 miles, you know, you're getting up to a lot of potholes. So if you're around 20 in the mean, you would think it'd be very unlikely that you're going to have a scenario of over 100 potholes, right? So then let's do our Poisson calculation. We're going to say this equals Poisson.dist. And we're going to pick up the X, which in this case is going to be 0, the mean is 20. I want to make that an absolute value. So I'm going to select F4 and the keyboard. So when I copy it down, that 20 will not move down. Dollar sign before the V, dollar sign before the 1, comma it's not going to be a cumulative, so therefore not true, but rather false because we just want the occurrences of 0 only, not everything up to 0, even though 0 is the first one here. So we want to say false or we could put a 0 for false. Closing it up, 0 is easier to do I think if you get used to it, but you could type in false if you want. I always misspell false, which is another reason. And then the whole classroom laughs at me because I can't spell false even though I do true, false, whatever. Any case, home tab and then number, let's percentify this thing, add some decimals. So there we have it. So if I scroll on down and if we total this thing up, total, it should add up to 100. Let's do it this way, alt equals, I have to click off.