 statistics and excel poisson distribution excel function and graph get ready taking a deep breath holding it in for 10 seconds looking forward to a smooth soothing excel here 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 crunchy numbers is my cardio product line now i'm not saying that subscribing to this channel crunching numbers with us will make you thin fit and healthy or anything however it does seem like it worked for her just saying so subscribe hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine if you would like a commercial free experience consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com 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 starting in a prior presentation so you could go back there or you can open a blank worksheet from here and you should be okay as well if you 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 to the heart of the practice problem blank tab is where we started with a blank worksheet and are continuing with it here let's take a look at the example tab to get an idea of where we will be going we've been talking about the poisson distribution and we've been looking at different families of curves in a prior presentation looking at a uniform distribution now the poisson distribution which could be useful to help give predictive power about certain data sets if certain conditions are met we talked about some of those conditions in a prior presentation we looked at the formula which you don't want to be too intimidated by because if we know the conditions that are met we can use what we're going to work on now which is the poisson dot dist function will then plot a graph and then in future presentations we'll use another kind of random generator tool that's a little bit more complex than we've seen in the prior presentations to generate some data that we can plot as well let's go to the blank tab and so like I said we have this information from the prior presentation but if you don't have it that's okay you can start from just a blank worksheet so we're going to then just say our condition to be able to use the poisson dot dist is to have the mean which we might have access to from prior data so oftentimes a poisson distribution might be useful when you're looking at like say line situations and you're trying to determine how many people might show up in a given interval or what is the likelihood of so many people showing up within a certain interval and so what we need then is the mean which is often represented with our sigma or our lambda with the poisson distribution it's often the lambda so i'm going to be then entering the lambda i'm going to go to the home tab we're going to go to the i'm sorry insert tab going into the symbols i'm going to make a symbol and the lambda is right here because it's recently used if you don't have it recently used you can look under the greek and coptic to find your lambda let's insert it and so there it is i'm going to say close this and then i'm going to enter and then go back into it and i'm going to put the equals next to it and then the cell to the right i'm going to say that that is 10 so we know what the mean is represented by lambda and that's going to be 10 i'm also going to say the x number of rows number of rows in our table uh i'm going to make it conditional we'll start off with like 29 rows and it will use a little bit of a formula and that'll help us to basically adjust our graph when we put our graph in place so i'm going to say this is going to be x and actually let's put it to the right here i'm going to put it to the right here i'm going to say this is going to be x and this is going to be the uh result of our formula uh which will be result here okay which i might maybe i should put f of x let's say f of x here okay all right let's center these i'm going to center these up top home tab alignment group i'm going to center it and then we'll go into the font group i'm going to put this and make it black and white because it's a header that's what we typically do with the headers i'll select these two and just make them blue and bordered which is what we often do for our other data input areas home tab font group bordered bucket drop down if you don't have that blue more colors standard there's the blue that i typically use you can use whatever color you want i'm going to make the end a little bit skinnier so we have a little bit skinnier of an end holding control scrolling in a little bit so we'll zoom in a bit so zoom in a bit more and so okay so then the numbers i just want this to be numbered from a zero down to 29 now there's actually a formula for that which could be useful when we're then going to make a graph out of something because then it could adjust the the the amounts that are picked up in the graph so let me show you what i mean here instead of just doing this which is what we will typically do in the past which would be just say zero one two and then using excel to memorize the sequence if i want this sequence to change i can use a function and then pick up this number to change the sequence so let's show you that we're going to say this equals sequence sequence this is the formula so there it is and then i'm going to say the number of rows is going to be 29 plus one you have to have the plus one because there's going to be a zero in there as well so we started zero up to 29 which means there's 30 rows but 29 plus the zero and then the columns is going to be nothing in the columns because i have one column so i could put a zero there or just two uh commas to get to the next point and then we're going to be starting at not one but zero which i'll just hard code or type in so i'm going to say okay close that up and enter and then it gives us our little spill down here down to 29 now again the reason that's nice in this case is because then i can change this to 30 and it'll then populate down to 30 i can i can i can decrease this to five and it'll give us uh whatever result we want which is great let's bring it back 29 now we'll use the poisson function so i'm going to put my cursor in p2 and to put that in place we're going to say equals and then poissons we want poisson dot dist double clicking on that the arguments we need are the x value which is going to be in this column so in this case zero and then the mean represented by lambda 10 in this case and then we'll talk about whether it be cumulative or not in a second so we're going to pick up x so i'm just going to pick up that zero going to say comma and then and by the way we will go back into this in a second and represent x with an array and that could be useful when we're graphing it but for now we're going to pick up that o2 and then comma the mean is going to be this 10 now that 10 i don't want it to move when i copy it down therefore i'm going to select f4 and the keyboard dollar sign before the m and the one and then we're going to say comma now it could be cumulative or not so if it's cumulative this will make more sense possibly when we look at the graph and we'll look at more examples shortly but cumulative means it's going to be all the results up to a certain point whereas if it's not cumulative you're just going to get the results for one particular point so you can type in true if you want it to be cumulative or false if the probability mass function we want it to be false so i'm going to just type in a zero you can also type in a zero or a one zero for false and one for true closing it up and then enter and so there we have this i'm going to then then i could double click right here to copy it down to how many cells we have you could make it a percent home tab number percent because that shows you a few more places out and then add a couple decimals so this is so this is what we have as our results and so what this is basically saying we'll get into more examples in future presentations is if you're in for example a line type of situation and you're trying to say that x represents a time frame say minutes what's the probability that two people show up within the two minute time frame something like that and the probability being point two three percent right and so when you're looking at the cumulative proper probability then it would be adding up the probabilities of zero one two three or four we'll talk about more examples of that in future presentations for now just note that one of the problems if i'm not using like an array is is notice this if i go down to 29 you get zeros after that point now if i wanted to bring this back up to like five notice that because i copied this side down then it doesn't really it doesn't shorten this column when i shortened this column whereas if i use an array it will shorten it so for example if i put this back to 29 and let's redo this formula so i'm going to delete this whole thing and i'm going to do it again slightly different using our array so i'm going to say this equals hoisin.dist tab and this time instead of selecting that just that cell i'm going to put my cursor there hold control shift down arrow that takes me to the bottom if i want to get back to the top i can hold control backspace takes me back to the top now i see an array in our formula is what we're doing here and then i'm going to say comma the mean is still this cell 10 i want to make that absolute still so i'm going to say f4 on the keyboard and then comma and this is still going to be zero or false closing it up and enter so now you can you can see it populates with an array same results but one of the benefits of doing it this way might be a little faster sometimes the arrays cause problems when you put tables in place so there's pros and cons to them but one pro is one benefit is if i put a five here notice it changes the whole size of my of of both columns which is kind of nice so let's put it back to 29 and and so then let's go ahead and graph this thing so i want to graph this i'm going to close this up a bit let's make this a little smaller so i'm going to select my data like so and then i'm going to go up top and say insert charts and i want to make basically just a bar and so we'll put this in place i'm going to have to adjust the data that it's picking up here so i will then go into it here chart design i want to select the data here's the data that it's picking up sometimes it's easiest just to delete these two and re enter it so i'm just going to delete both of them uh and say just remove both add a new data set and i'm going to say the series name that we want represented on the y is going to be the f of x as the name and i'm going to pick up the data putting my cursor on uh p uh to holding down control shift down which takes me to the bottom of the data set control backspace takes me back up okay and so there we have this and then this data set i want to make sure that i have my own numbers in there instead of their numbers right so i'm going to edit this data set and say i want you to be picking up from this area uh control shift down control backspace and then enter so now it goes up to 29 because that's how many numbers we have there and okay so there we have it and then i'm going to say boom this is good and then uh let's just call this let's just name this uh poi let's just call it up top poi son poi son i hope that's spelled right again okay and then and then down here we have the x so we could then say okay let's add the uh we could add the axis titles and say that this is going to be uh equal to f of x and this is equal to uh x and then so so there we have it and you can see it looks kind of like a bell curd but it's basically going to be skewed a little to the right now once we have this then we can adjust this this one number the mean and that's what's great about the poi son distribution that's what you that's what you need to know in order to you know populate the poi son and so i can then say well what if this was uh what if this was free or something like that you could see then it's you could see the skew a little bit more uh in detail here now note that what's interesting is this still goes out to 29 because i told it to go out to 29 here even though we have a lot of information that we might not need on that tail end even though it's possible like if you're talking about a situation where where you're thinking about how many people might show up uh in a given interval of time it's possible for this number to go up to infinity but obviously there's some kind of practical limit right so i could then change this and say well it looks like i can basically cut this off at maybe like 14 and so that's going to adjust our graph down a bit so if i look at our graph notice it's still picking up the information down to here but if i then adjust this up i'm going to say boom and now our graph has been adjusted in terms of the x column which is fairly easy to do so let's go out further let's say this went out to like 70 and then i'm gonna and then so so now it populated uh down to here the spill uh looks good but i need to populate i need to adjust the formatting of all these cells let's just paint brush all of them and then make this one black and white and centered so now now it's going all the way down to 70 and you can see that actually populated automatically in the graph to pick it up to 70 so when i shrink it it might not do it exactly uh automatically but you get the idea and then if i bring this up to if i if i brought it down to like one or something like that you could see that kind of highlights the skew more if i bring it up to five you can bring it it looks more kind of bell shaped but it's always going to be skewed a bit so that looks pretty bell shaped but there's generally going to be a right skew on it which you could see more dramatically when the mean is smaller here so there we have it so there's our there's our our general table with it so now uh next time what we'll do is we'll use a a random generation tool that still has a random element to it but it's going to be designed for randomness related to a poisson uh distribution and we'll and we'll just so we'll generate that which will simulate a situation that basically meets these conditions still has randomness to it but the idea is that it simulates randomness where these conditions are met and that random information that we get then should then if we plot it be similar to this outcome in a similar way as the uniform distribution you know the straight line distribution would be similar to something like rolling the dice that we talked about uh uh last time and that's the idea because now if we can see that relationship then we can possibly use a poisson distribution to give us ideas about what to be expected in the future even though it's not perfect there's still randomness to it but it might give us that approximation where they could give us better decision making capacity