 statistics and excel poisson distribution formula get ready taking a deep breath holding it in for 10 seconds looking forward to 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 crunching 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 yeah 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 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 to the heart of the practice problem blank tab blank worksheet so we can practice format in the cells within excel as we work through the practice problem let's look at the example tab to get an idea of where we will be going we've been thinking about different families of curves that might be useful in different situations to help give predictive power last time talking about a uniform distribution which is a very easy formula and curve to look at we have the straight line this time a little bit more complex curve the poisson distribution so we'll start off just listing the conditions in which a poisson distribution may be useful we'll make the formula which can look intimidating but we just want to give the formula and and then note that in practice it might be useful just to know the conditions where the poisson distribution would be useful and then apply the poisson distribution possibly with an excel function such as poisson dot dist then we will be taking a look at some of the components of the formula and how we might represent them in excel and then in future presentations we'll actually use the poisson dot dist formula to create a table and to create our graph over here which we can then change uh with our factor up top and then we'll actually also generate some data not with a random generation tool or not the same formula we used in prior presentations but a different kind of random generation tool that will be in alignment with a poisson type distribution so that's where we're headed let's go to the blank tab so first we just want to list out the conditions up top i'm just going to copy and paste them you can type them in uh if you so choose these are the conditions where a poisson distribution might be useful i won't go into them in detail here because we talked about them in a prior presentation but just remember if these conditions are met then you're thinking okay maybe the poisson distribution can give us predictive power about a certain data set right so an event can occur any number of times during a time period events occur independently the rate of occurrence of occurrence is constant that is the rate does not change based on time the probability of an event occurring is proportional to the length of the time period all right let's actually build the formula now i'm going to use our our tool to do that insert and we're going to go into our uh equation and then here's our equation i'm going to do an ink equation and just type it into our ink equation again so i've got the writer tool down here i'm just going to say this is going to be p of x boom and then i'll put brackets around it so p of x that looks good so far equals and we're going to say then we need i'm going to put a lambda here which is often used for the mean here so i'm going to say lambda and then to the x and then times e which we'll talk more about in a second e to negative lambda see if i can that's it well it's still picked it up look at that and then i'm going to put that over x and then factorial which is an explanation so this looks like more of an intimidating type of formula here but we just want to get an idea of the formula and of course we'll be using functions as well so let's just take a look at p of x equals then lambda to the x times e to the negative lambda over x factorial now the lambda is basically going to be uh the mean which we've represented in the past with a mu which looks like a u in essence uh so or an x bar so you could possibly see those uh in the case in in place of a lambda but the lambdas often used when we're talking about a poisson distribution e as a constant similar to pi so we'll talk more about that in a second and then the exclamation is a factorial and we'll talk more about that in a second so that looks good let's go ahead and insert it so i'm going to pull this to the right let's make it a little bit larger home tab font group and let's just bring it up i'll just say up to like 24 okay and then i'm going to make it a color so i can't see the grid lines in the back because i like the grid lines but i don't want to see them in my formula so i can go to the home tab font group and then in the bucket drop down let's make that like the orange now i'm also going to format the entire worksheet and then we'll talk a little bit more about some of the components of this formula to get an idea of what's going on with it so i'm going to select the entire triangle right click on the selected area format the cells and then i'm going to say this is going to be currency negative numbers bracketed and red no dollar sign i'm going to get rid of the decimals for now and then add them as needed okay i'm also going to go to the home tab font group and make the whole thing bold so there we have it now let's just talk about some of these components in our formula remembering not to be too intimidated by the formula because we will be using excel functions so if we can see where the poisson distribution is applicable then you can use the excel functions possibly instead of you know using a formula calculation but just to represent these in excel remember that or the e is a constant so it's a constant and it's and we can do a formula in excel to get that e constant so it's kind of like pi that it has that constant i'm going to make this cell a little bit smaller so the formula is going to be equals exp and then i'll just put a one there and there it is and enter now if i add decimals home tab number group adding decimals you can see it it basically goes on forever so we can approximate it to however many decimals using excel is great of course because it allows us to use you know the actual number so then we're going to say that the factorial which is represented with an exclamation mark so that's just it's not like it's rep the exclamation and the e it's not like those are representing you know other other numbers we're going to be plugging into there right the e is the constant kind of like pi and the exclamation is a factorial so factorial orial so if i said five exclamation what does that mean that would be like saying five factorial which is like if i list out five four three two one then we would just multiply these out right this is going to be equal five times four this is going to be equal to the result of 120 times three this is going to be equal to the result of 60 times two and this is equal to the result of 120 times one which of course is still 120 so five factorial is 120 so we can also just so we can see those those functions e if i was to put an excel function in excel would be an equal sign but i want to see the formula so to see the formula i want to put this here and then the equal sign of exp and then a one that's that's going to be the function that allows us to calculate the factorial here of 2.71 so on so forth i mean that it allows us to calculate the e that we did up top here and then the factorial there's a function for that and that's going to be this so i can see the function equals factorial and i'll just close up the brackets like that so there we have it so if i if i did that for factorial of five it would be equal to factorial brackets five close the brackets which gives us that 120 that we got up top okay so then we've got the mean so let's put this let's put this over here so we can see it i'm going to make a skinny column and i'm going to say we have the mean might be represented by a mu or or a lambda which we have up top so let's take a look at how we can insert those there's a couple ways we could do it we saw about in a previous presentation but i think the most common way would be going to up top insert and then go into the symbols and then i've got the normal text on this side and i'm looking at the greek and coptic greek and coptic and i'm looking for these letters i have them down here because i've been using them quite often so i can't i don't know the greek alphabet so it takes me a little bit of hunting and searching but the mu is like the the uh an m so if you search around a bit if you go into greek and coptic you'll find these there's the mu and then there's the lambda right so i can pull in like a mu let's do that insert and then it puts it down there and then i'm going to hit enter so it doesn't mess up my formatting and then i'm going to go back into it and say or and then let's pull in the lambda so i'm going to go into the insert up top symbols and then the lambda is going to be next to it in the greek and coptic there it is and now both of those should be pulled down here into my recently used symbols and it'll be a lot easier to find if i need to represent those so there's those two and uh and then we've got the variance which you'll recall was sigma squared so normally i would go to the home tab symbols and then symbols and then again usually i would have uh the sigma down here but if i was to go up and find the sigma up top so here it is and then once you once you insert it it'll be down here in your favorites so we'll say okay then i'm going to hit enter to square it i'm going to double click in here put a two and then select that two just the two not the whole cell right click on it right click on the two and format it and i'm going to make it a subscript boom so now we've got sigma squared for uh the variance so so there we have uh that and then uh so for the point let's say for the poisson the mean and variance are equal so for the poisson distribution then the mean and the variance are generally equal which is could be a useful thing to know so we can say that the mean often represented by a mu or with the poisson distribution a lambda so let's enter that insert symbols symbol the lambda insert and i'm going to say equals so it doesn't mess up when i type something else into it double click equals and then the variance sigma squared insert symbols the sigma is now in recently used insert and then close this out enter going back into it typing a two selecting it right clicking on the selected area and saying that we want to format it as a subscript and enter so so there we have it just just a couple just some of the uh of the information for that could be useful for the formula now in the following presentation what we'll do is we'll get right into using the function which is going to be equal to the poisson dot dist right and that's what we really want to go but some of these tools could be useful when you're when you're then trying to analyze some of this stuff and you want to represent something with uh the the the mean as a lambda or a mu or the variance and the sigma square and so on and so forth within excel so there's just some ways that we can populate that i'm going to then highlight this and go to the home tab font group we'll make this blue and bordered as is our general custom and then these items let's make these blue and bordered so i'll select these to here maybe and make that blue and uh bordered as well let's do a quick spell check review spell check it constant constant uh let's change that poisson for i don't that's a name i'm going to ignore it for now mind i'm going to ignore for now variance let's change that and okay so there we have it so hopefully i spelled poisson distribution correctly and we'll continue on with it next time