 Statistics and Excel. Binomial distribution. Multiple x drive to work in traffic. Example. 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 that's okay, whatever. Because our merchandise 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 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 down to the heart of the practice problem. The blank tab, blank worksheet 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 what we will be doing. We're considering a binomial distribution once again with a scenario that might not be as intuitive as some of the prior scenarios. Thinking about driving to work and whether we're going to be caught in traffic or not. Notice we're not asking how much traffic there might be. We're saying either we're going to be in traffic or not in traffic. Therefore we have that kind of binomial type of situation. We're defining success then as not having traffic and a fail as basically being in traffic. So we'll say drives to work. This is going to be in. We're going to say five as if five days a week we're driving to work. And then we've got the probability of success. We're going to say is only 12% probability of success. Success being defined as no traffic. And then X is the number of times no traffic out of five. So we're looking for three times three days out of five with no traffic. And that's what we'll be looking for here. And then we'll graph this information. And we'll add some of the new step being that we're not looking for one point but asking questions of X being say greater than two or greater than or equal to one and so on and so forth. And we'll take a look at a couple different ways that we can get to the answers of these types of questions down below. All right, let's go to the blank tab and get to work here. We're going to select the entire worksheet formatting the worksheet as we do every time selecting the triangle to do so right click in the area formatting the cells. Let's go to the currency negative numbers bracketed and read no dollar sign to start off with and no decimals to start off with. Okay, I'm going to make the whole thing bold to home tab font group and bold in the entire thing. All right, so then we'll put our conditions up top. Once again, we're going to say that in is going to be equal to the number of fixed trials, which is going to be equal to drives to work. And I'll set in to be we're going to double click on a. And I'm going to set in to be five five drives to work P is going to equal the prop for probability of success, meaning and success is defined as no traffic. The probability of no traffic is sadly only 12%. It's pretty sad home tab number group. We got it. We got to find a job closer to home or something's getting this is driving me crazy. X is going to be equal to the number of times no traffic traffic out of five. So if we're looking for the number of times what can say three. So the number of times no traffic three out of five three out of five days driving to work will say we have no traffic. We're going to put some borders around that. That's our conditions home tab font group. And then we can border it like so. Okay, so then I'm going we could plot this out. We can simply plot it out if we so choose if I'm asking that one singular question, I can then use my binomial formula to answer it. All right, well, this is going to be equal to the binom.dist and I'll show each of these ones that we can use. But first, let's just take a look at the binom.dist.range because it's the more flexible and latest and greatest one. So the number of trials we're going to say is five comma the probability is 12% per trial comma. And the numbers we just have one at this point is going to be the number three. Now this formula allows us to have multiple X's and that's why it's a little bit more flexible. And so we'll take up more of a deep look in that in a second. So that's going to be that one. And let's go ahead and home tab number group percentified add some decimal. So the likelihood being 1.34. I'm going to say this equals this and let's do it again with the other formula. This equals binom.dist. This time we'll use this top one though still, you know, it looks, you know, it's fairly current, but not the latest and greatest one, right? And so we'll look at the two of them. The numbers is going to be this and then comma the number of trials is the five and the probability is 12. And then this one has the cumulative, which you will remember if you went through the questions that we looked at for the Poisson distribution. We'll talk more about this later in a second here. But in this case, I don't want it to be cumulative. I want to type in false or I can put a zero instead of typing in false. And if I format this the same way, home tab format painter, we get the 1.34. Now, obviously we can plot this as well as we've seen in the past. So I'm going to go to column D to do that and say I have X and P of X. Let's say making this black and white home tab font group black, white, let's center it as well. And then I'm going to enter our X is I'm going to do it in a fancy way. So instead of just saying 12345 or 012345, I'm going to say equals sequence. And we're going to say that the rows that we want are going to be five plus zero. So plus one, so we'll have six of them zero through five comma no columns. So two commas, the start point is going to be zero and close it up. And that gives us our our fancy spill array numbering that out, which could then change if I change this number. So fanciness happening here, although it does make it difficult to put a table in it. So there's pros and cons to it. We're going to do our bi-nome. I'm going to do the fancy bi-nome with the dot range, the latest and greatest one. And we'll say that this is going to be the trials are going to be five. I'll select F4 on the keyboard to absolute reference that so I can copy it down comma. The probability is 12 F4 in the keyboard dollar sign before the B and the two so I can copy it down without moving that down relative comma. And the numbers is going to be this range. And so this will be a spill array. And I can just say enter and it spills that down. I select these items, home tab numbers, percentify, add a couple decimals, and there we have it. So this is the one that we were looking for when we looked for that just one number. But now we can see everything from zero to five. So the likelihood of having zero out of five traffic when driving to work is 52.77 zero successes. That is, meaning we have zero times that we go to work and there's no traffic. One time that we go to work and there's no traffic that glorious day one out of five 35.98 and then two times 9.81. And to have five times with no traffic given the fact that there's traffic all the time. The likelihood is 12% each time to have track traffic is still less than less than 0.01%. So there it is get used to the traffic. We've got our audio books on, you know, our headphones, you know, so we're blasting our audio books and learning stuff in the car. So it's okay. I like the traffic. I like it because that's my that's my time. But in any case, I'm going to then insert here. We're going to go charts and let's add a chart this time. Let's add this kind of chart just to switch things up a bit. So we'll say let's add this one. And so there we have it and pull this over and I'll change this so I can get a zero down here as we normally do. Selecting the data, I want to pick up my own X so it picks up the zero to five and okay, okay. And there we have plotted our data and of course it's somewhat flexible. So if we changed some of our our information over here to like, what if this was 30 or something like that, you could see the changes in the graph. Let's bring it back to 12. Okay, so now we're going to ask questions such as let's go down here and say let's say we ask questions of of P. Well, let's just call it P of X. And then I'll make this a header area. Let's pull it down one. Let's put it down here. Make up your mind, man. Okay, home tab font group black, white, and let's say that we now want P X to be equal to two. So, so that one's a straightforward one. So instead of three, we can have it equal to two. And I can see, you know, the outcome up top. But of course we can calculate it multiple different ways. So let's just do that one a couple different ways now so we can see some of the different calculations we can use. So one is our the same one we did before we can say this is going to be the binome.disk.range the latest and greatest one, which is going to be the number of trials five comma the probability per trial 12 comma the numbers. Now I'm just going to type in two and enter. Let's make this format painted home tab numbers, dollar sign, decimals. There we have it matching out to that 9.81. I'm going to pull this to the right a little bit. Now I could use my other formula. This is this is going to be equal to binome.disk not range but just dot dist. And this one, it still could be kind of useful because it's similar. It doesn't use the arrays and it's similar to what we saw with the poisson. So you so we have the numbers then the numbers are going to be two comma the trials are going to be five comma the probability is going to be 12. And then you've got this cumulative or non cumulative in this case we don't want it to be cumulative so we can type in false or put a zero and there it is. Let's format paint this over home tab format painter and put that here. Now another way that you can you can do this is you can actually if you use the the latest and greatest formula you might try to define the lower and upper of the range and then use basically bins or use the binome. And you can kind of copy the binome down in a relative formula. So we'll take a look at that as we go in this case it would just be the lower and the upper would be two and two because but but we'll look at some different ones right here so home tab font group black white. Let's center it. And so this would be equal to I'm going to do this again binome binome dot disk dot range. And now I'm going to say that the trials are going to be five and I'll say f4 on the keyboard so I can copy it down comma the probability is 12 f4 on the keyboard so I can copy it down. And then comma and then the numbers are going to be the numbers are going to be and I'm going to pick up the range now of these two. And so that's what's a little bit different with this formula and I can hit enter and I'm going to make that a percent home tab number group percentify it and then open that up. So let's do another one a little bit trickier one I'm going to say this is going to be P of is going to be less than or equal to two. So he's going to be a less than or equal to two one way I can do it I could I could these are the two binome ways that I can do it. Let's add one more area I'm going to take this and drag it to the right. And I'm going to pull this a little bit over here and then I can also use my basically the sum function this would be equal to this one up top right. I could do it that way. And I can do this. And so we could say all right if I was to do that. Let's first define our ranges here so we're going to say this is greater than or equal to two so the lower range then it's going all the way down to zero. The upper range includes the number two there's our upper range. So if I was to sum this up one way I can do this is just to say well this is going to be the sum of zero down to two. Enter and home tab numbers percentify add some decimals we can use the fancy formula equals the binome dot dist dot range. And this one is more versatile but it's a little bit confusing because it's a little bit different than what we saw with a poisson when we're doing something like this right. So we can say OK the trials are going to be the number of trials is five comma probability is 12 comma and now I have these two numbers I don't have that cumulative argument. So this I can pick up both of these of the zero and then comma and then the two instead of doing the cumulative thing. And that if I go home tab number percentify add some decimal we get to the same result. Now if I used the older formula which is similar to what we had with poisson with a cumulative component note what we have to do we have to say OK this is going to sum everything up to a certain point. Now if I'm just summing up to the number two the cumulative works good where it runs into problems is if I'm trying to find something like in the middle just these two numbers. So so the cumulative formula might be even easier with the binome the old binome binome dot dist which would be equal to the number the number that we want now is two. So I can say I might put this to comma the number of trials is going to be five and then comma the probability is 12 and then comma. Now this time we want it to be cumulative because we want everything up to and including two. So therefore I'm going to say true or I can put a one for cumulative and enter and we should get it the same thing there. Now let's imagine we have one where P is going to be X is less than or equal to one. So we have a similar kind of process I can say OK what's my lower limit. It's going to be less than one so it goes all the way down to zero again and then it's going to be less than or equal to one. So I'm going to include one is the upper limit. So I could say all right that's going to be equals to the sum of zero and one format paint home tab number percent not format paint. I'm going to percentify it and then I could say all right. And then I'm going to do it this way equals the by no dot this dot range tab and the trials are going to be five comma the probability is 12 comma. And then the numbers I have two of them this lower and this as the upper and bracket it up close it out percentify add the decimals. There we have it if I use the old one again it's even a little easier maybe with the old one in this case by no dot this is going to be the numbers are going to be the upper one the upper limit. The trials are going to be five and the probability is 12 and then this one I wanted to be cumulative again going up to that point. Therefore I can put in a true or type one enter and percentify add some decimals. So there we have that one let's add let's do another one a little bit more complex we're going to say Px is greater than or equal to two. Okay so if it's greater than or equal to two then that means that the upper limit goes all the way as high as it can go which is up to five in our case. And the lower limit is including to because it has or equal to so the lower limit is to that it's going to be including. All right so I can do it this way equals the sum of everything from two on up to five percentify add decimals. Or I can say equals the the by no dot this dot range and I'm going to say the number of trials is five comma probability 12 comma and then the numbers I can say is this two comma and then this five and then close it up and enter. And percentify add and then I can use the old one and the old one gets a little bit more complex now because now I only have that cumulative component so what I have to do is take everything up to five right and then I've got to subtract out everything up to the so I have to take take everything up to five and then subtract everything up to but not including the two right so I'm going to take this whole thing minus this so I can do that I can say okay well that's going to be equal to the by no. Did dot dist and I'm going to say the number that we want is the upper limit of five and then comma trials are five comma probability is 12 and then comma cumulative therefore one brackets minus by no dot dist. And then we want the lower limit which I have here as two but I have to say two minus one because I don't want to take the cumulative up to two I want to take the cumulative up to one and then comma the trials are five comma the probability is 12 comma we want to be cumulative which is one. And so you can see this is why the newer formula although kind of more complicated in some ways to kind of figure out how that last bit works with the two X is is actually a lot nicer when you have to do something like this right and so I can then say. Percent adding some decimals let's do another one we're going to say P X is greater than two so we'll say X is greater than two not equal this time so if we do that that it's greater than two so the upper limit is five but now it's not including to so we have to say the lower the lower part is going to go down to. Less than it's going to be going from three up to five right. So if we do this we can say okay this is going to be the something of three up to five percent to five add a couple decimals or we can say this is going to be equal to the by no. By no. Dot dist dot range of trials five comma probability is going to be 12 comma numbers and then I can say this is going to be the three comma five that we have our buckets and percent to five add decimals. So or I can do it this way and this way I have to take everything up to five minus everything. Everything up to two because I don't want to subtract out what is in what's included in that three there so I got to take that three minus one. To get to the two in our case so I'm going to say this is going to be equal to the by no. Dist the number is going to be five comma trials is going to be five comma probability is going to be 12 comma needs to be cumulative so a one close it up minus by no. Dist number is going to be that three which you can't see but it's right there but I have to subtract one so it gets down to the two and then comma the trials are going to be five comma the probabilities 12 comma needs to be cumulative therefore a one and close it up so you can see that's a pretty tedious formula but so that's why the other ones a little bit faster. Now notice that with this new formula also if I delete this bit right here I can copy this down even faster which is nice. So let's do this one again and I'm just going to try to do all of it now that I have my upper and lower limits with one spill function so super fancy equals by no. Dist range again and we're going to say that the trials are going to be five I'm going to select f4 making it absolute I'm going to say comma and then the probability is 12 same thing we've done before absolute so I can spill it down without a problem comma and then here's where we have this argument where we were putting the top upper and lower ranges so this time I'm going to select this whole array for the lower and then comma and I'm going to select this entire array for the upper and it'll should spill out for us which is super fancy so we're going to say okay enter and it spills it down let's select these three and number group percentify and add some decimals and hopefully we got the same results for those methods and so there we have it so just a couple different ways that you can see that formula so I'm going to make this blue and bordered as we normally do if you don't have that blue it's in the color wheel right there. Make this blue and bordered I'll make this blue and bordered. I made it blue and white instead of bordered shouldn't be white. I can't see it man. All right blue and bordered. And then let's make this black and white or just all I need is black because there's no white there. Let's make this blue and bordered. Let's make this blue and bordered and we might be able to thin this up a bit get everything fit nice and tight in here. Okay so that looks pretty good let's do a spell checkie bean by no I'll ignore by no means do I need to do that by no means you mean there's actually two means because there's by no by no means by no means by no means. Okay so it looks pretty good.