 statistics and excel poisson distribution roller coaster line example get ready taking a deep breath holding it in for 10 seconds getting ready for a smooth soothing excel first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like our trust me i'm an accountant product line yeah it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot com 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 but 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 right for heart of the practice problem blank tab basically a blank worksheet except for this image on it but you don't really need that to move forward so that 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 working with the poisson distribution which often deals with line waiting situations as it will be with our example here line waiting situations for a roller coaster we're actually first going to be thinking about and imagining the mirroring of a situation where we're sitting with a stopwatch at the line to see how many people arrive during certain intervals and then we'll make observations of that data the observation basically being that it seems to be following a poisson type distribution at least for the most part and then we'll use our poisson dot dist to plot a curve in accordance with the poisson distribution and the idea then being that possibly the poisson distribution curve can help us to make projections and predictions into the future about this particular data set let's go on to the blank tab and start working our problem so we're going to say that x is going to be well let's first format the entire worksheet i'm going to put my cursor on the triangle right click on the worksheet we're going to format the worksheet as we do every time i'm going to make it currency and uh let's make it currency negative numbers bracketed and red no dollar sign i'm going to start off with no decimals and then add the decimals as i need them okay i'm going to scroll in just a little bit as well all right so now i'm going to say that x is going to be equal to the arrivals during one minute one minute i'm going to make the whole thing bold put in my cursor on the triangle home tab font group everything is bold and these are going to be arrivals for a roller coaster ride line that's what we're measuring so we're going to be sitting there with our stopwatch and we're going to be thinking and we're going to be measuring now or counting however many people arrive during each one minute time period so it gets a little bit difficult to wrap your mind around so you're imagining you're sitting there with a stopwatch and you're every one minute you're starting to stopwatch you're seeing how many people come in during that one minute time frame and we're marking that information down now we're going to want to try to mirror that information with our random generator in a similar way as we did with the dice ruling so we're not going to use a simple formula we're not just going to say this equals the random function between because the randomness has to be in accordance with the poisson distribution so there's still a random element but it has to be according to the distribution and in order to generate it we're going to have to give excel the uh the mean so the mean we're going to say is uh let's say the mean is going to be 2.75 and i'm going to add decimals to that cell home tab number group adding some decimals now obviously if we were sitting there with our stopwatch we might not know the mean that's what we're looking for but when we randomly generate the data we need to be able to have that uh in uh excel for it to be able to generate the data so we're going to now generate the data to do that i'm going to go into the data here i'm going to go into the analytics and you've got this data analysis if you don't have that you can add it you go to the file tab you go to the options down below and then uh you want to go to the add-ins on the left and then in the add-ins you want the add-ins here go and then you want to be picking that analysis tool pack and if that's checked off then you should have the analysis in the data tab analysis group okay so first i'm going to say where i want to put this so i'm going to say this is going to be the x data that we're calculating or finding let's make this black and white font group i'm going to make it black and white and center it and then i'm going to put my data right here and we're going to imagine we're counting a thousand uh periods of two minutes right so we got our stopwatch for a thousand periods of of one minute intervals so let's go to the data and then we're going to go to the analysis and data here and so now i want to have the random number generator and i'm going to say okay random numbers in accordance with i'm going to put one for the number of variables that's going to be the columns so i just want one column number of random numbers we're going to put a thousand of them so that means it's going to output a thousand numbers mirroring a thousand minute intervals that we were sitting there with our stopwatch and then we're going to say that we want it in accordance with a Poisson distribution and lambda is going to be the mean which i'm going to put at 2.75 so we need that condition we wouldn't have that if we're sitting there with our stopwatch although we might have an idea of what it is given past given you know the past performance as we're sitting there at our line putting people on the roller coaster and whatnot so i'm going to put this on e3 and so there we have it and that's it so let's go ahead and say okay so now we're imagining that we're sitting there and we we have our stopwatch and for the for the first minute four people arrived next time three people arrived and then two people arrived notice that the mean over here is not a whole number right obviously uh you know it's because it's an average so if we're sitting there with our stopwatch it's not like 2.75 people can actually arrive within a one-minute time period right because we're not it's not like we're gonna half count someone if they like are missing a leg or something that says there's still a whole person even if they don't have like a limb anyways you know what i'm talking about so we're gonna say so every one minute so in this one minute six period people arrived in this one minute three people arrived and so on and so forth so this was in accordance with a poisson distribution of a mean of 2.75 but there's still that element of randomness to these generated numbers okay so so now what i'm going to do is say let's put these into like our buckets so i'm going to say this is our data we're going to say these are the number of arrivals and this is going to be the frequency and then we'll have the percent of total over here i'm going to make this into headers so i'm going to select these items home tab font group black white we're going to center it and then i'm going to wrap it all right and so then we're going to say okay the number of arrivals i'm going to notice when we think about these arrivals at a ride it could go up forever in one minute time period you could have infinite number of people show up in theory but that's not in practice what's actually going to happen because it's going to taper off at the tail end as we go so i'm just going to go up to a reasonable number let's just go up to like 29 let's say so i'm going to start at zero you could have zero people show up in a one minute time period one and so on and so forth i'm going to put my cursor here and drag it down to get to 29 let's go to 29 why 29 i just picked it randomly then we're going to use our frequency which is our buckets remember that you could use you might say hey look i'm going to use the count if function which would look like this equals count if brackets the range is this shift i'm holding control shift down and then control backspace to get back up comma criteria is that and then enter however sometimes when you use that formula like sometimes it gives us a number that it's not picking up because like it's not a whole number or something like that so the frequency still function is a safer thing to use typically so i'm going to say no let's not do that let's use the frequency which is going to be equals frequency and this is going to be an array function and i'm going to pick up my data array i'm going to put my cursor in e three kind of hold down control shift down arrow takes me down to the bottom i want to get back up to the top without unhighlighting this holding control backspace getting me back to the top comma and then the bends the bends are going to be starting on g3 holding control shift down takes me down to about 29 to get back up holding down control backspace back up to the top and enter so now it spilled it down i don't want it to go down to 33 here so i'm just going to cut off the last bit so it's going to i'm just going to say bring that to 31 and see if that so now i've got it nice and even so that looks good now i can double check if my numbers make sense because the total here should add up to uh a thousand because i spit out a thousand numbers right so i'm going to equals the sum i'm going to use my keystroke fast keystroke alt equals sums up sum it up the right area enter a thousand so it looks like it's picking up uh the right numbers and then i can look at the percent of the total if i look at the percent of the total i can say this equals this divided by divided by the total and then i want to copy that down so i'm going to say f4 so i can copy it down and then enter and then i will percentify that home tab numbers group percentified add a couple decimals possibly double clicking on the little fill handle copying it down so there's uh the percentages of the total so when we did art when we sat there with our stopwatch we counted for every one minute you know for a thousand one minute time intervals 62 times zero people showed up which is 6.2 percent 171 times one person showed up which is 17.1 percent 243 times out of the thousand minutes we sat there with our stopwatch 2243 showed up and then three people showed up 232 times which is 23.2 of the total and so on and so forth so now that we have our data then we can say okay well what if i was to plot this data you know what what would it look like if we were to plot it and notice i could plot either one of these so i could say let's take the actual numbers and i'll bring it down to here and so i'm going to say let's say insert and then go to the charts and then i'm going to make this a bar chart and we'll say bar chart and there it is so i've plotted uh the frequency and let's go into my data i'm going to go into my data up top i'm in the chart design data and then the frequency looks good i'm going to adjust this one however to pick up the range that i want which is from zero here down to the 29 uh control up i'm sorry control shift down shift up to pick up just down to 29 okay and then okay so so there we have it and i can look at that and say hmm i could see you can see it's kind of skewed uh to the right here and it's got that distribution that looks like it might be you know a poisson distribution i can also do it this way with the percentages if i select all the percentages and i insert a bar chart right i can go hmm let's do this with the percentages and i'll do the same thing data tab this looks good i want to make the data over here on g3 control shift down shift up enter and okay right so you've got now you've got it in terms of percents versus the the whole numbers and so that's one indication that if we start looking at our data and say hmm that you know it looks kind of like it might fit a poisson distribution why might that be useful because the poisson distribution will allow me to have uh an even line uh that will allow me to make predictions more you know more easily because now i can now i can make predictions based on you know a function as opposed to you know just randomly generated numbers right if this if i did this and it came up to some jagged landscape looking thing then i wouldn't be able to plot any kind of a curve on top of it with a nice fairly relatively simple equation i know the poisson equation doesn't look you know really simple but it equates to a nice smooth line as opposed to you know a jagged landscape looking thing which would be much more difficult or a complicated type of thing you need calculates to to do it right okay now i'm also i'm going to move this to the right now i'm going to move these to the right so that's going to be an indication for us that we might be able to use poisson another if i i'm going to make column j a little smaller and i could say okay what's the mean of the data so now i can calculate the mean now if i was to do a poisson curve then i'd have to know what the mean was remember that we took the mean in order to generate the data but in real life i wouldn't know the mean possibly and i would have i could guess it right i have an idea maybe but then i now i can calculate the mean based on this data right so now that we sat there with our stopwatch so i can say the mean is going to be equal to the average brackets of the data control shift down and enter so now we just took the average and if i add some decimals home tab uh uh number group adding some decimals it comes out pretty close to 2.75 right and that and so that's what we need we need that mean in order to plot the poisson curve so now that we have our data we can calculate the mean and we can plot our uh curve now another indication that we have a poisson situation is that the mean is going to be equivalent oftentimes or roughly equivalent to the variance so to calculate the variance let's take our data remember what we did is we we basically take all of our data and we subtract it from uh from the midpoint the mean right so let's just do that real quick i'm going to add some cells to do that i'm going to put my cursor on f to g right click and insert and then i'm going to say now this is going to be the mean and then i can say okay now this equals that 2.73 i'm going to say f4 on the keyboard and copy that down and then i'll just subtract every point from the mean difference call it the difference so this equals this minus that and then i'll copy that down boom and then if i scroll down to the bottom we can then say okay all the way down i can just hit control shift down all right so then we have our totals and so so this i can do the count i can do a count here if i want equals count just to put something here count and i should come up to a thousand and this is going to be the uh sum i'm going to say alt equals to the sum it's going to be zero because that these are showing the amounts that are above and below the mean i'm going to add another column putting my cursor on column h right click and insert and this is going to be the uh squared amount squared so now i'm going to take this carrot to the second power or squared and double click copy that down and then if i go down to the bottom we're going to say all right let's sum this up which is alt equals so now we've got uh uh that amount so this is going to be the squared uh this is the squared uh sum of difference let's say which is equaling that and then we're going to divide by the count which is we represented the formula as in which equals 1000 right so now i can divide this out and this is going to be the variance sigma squared equals this divided by this i'm going to add some decimals and you could see that comes out to 2.55 which is fairly close to the mean right and then if i and so that's going to be the variance and if i take the the standard deviation you'll recall that we take equals this or i'm sorry the square root sq square root of that right and that's going to be the standard deviation but right now we're kind of looking at that variance because if that equals the mean that's an indication that we're in this poisson distribution situation now i can also if i wanted to calculate that variance this way i could do it with a function right i can say equals the ar variance of the population double clicking that and then take this whole column of our data control shift down shift up so i don't pick up the total enter and so i added a couple decimals 2.55 here's the variance s equals variance of a sample and then again i'm going to put my cursor on here control shift down shift up so i don't pick up the total and then add a couple decimals right so the idea here so if i looked at this data set these are going to be the indications that i have a poisson distribution kind of situation i could say okay yeah it looks like it the curve looks kind of like a poisson distribution uh it's slightly uh it's slightly skewed to the right i've got a if i calculate my variance and the mean those are roughly equal and in a perfect poisson relationship those would be equal so the closer those two are together the more i'm going okay maybe i can use a poisson distribution and if i do that then i can i can plot the an actual smooth curve and instead of a jagged data curve right and so that's what we'll do uh next time