 Statistics and Excel, binomial distribution, multiple x drive to work in traffic example. Got data? Let's get stuck into it with statistics and excel. You're not 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 accountinginstruction.com or accountinginstruction.thinkific.com. Required to you, but if you have access to one note or in the icon left hand side, one note presentation, 1570 binomial distribution, multiple x drive to work in traffic example tab. We're also uploading transcripts to one note so that you can go into the view tab, immersive reader tool, change the language if you so choose, be able to then either read or listen to the transcript in multiple different languages, tying into the video presentations with the timestamps. One note desktop version here in prior presentations, we've been thinking about how we can represent different data sets both numerically with numbers such as the average or mean median quartiles and pictorially using tools such as the box and whiskers and histogram, noting that the histogram is generally the tool that we visualize when thinking about the spread of data and then we can describe the spread of the data on a histogram using terminology such as it's skewed to the left or it's skewed to the right. We're now looking for those mathematical equations representing curves that sometimes can approximate actual data sets in real life depending on the scenario and if they can we would like to be able to do so because those curves can give us more predictive power about whatever it is the data set is working with. This time we're going to be looking with a binomial type of distribution. We looked at the conditions for a binomial distribution, one of them being that we would have to be able to organize our information into a success or fail situation such as a coin flip, having a success or a fail defined as success possibly being heads fail being tails. We talked about a sales call situation where for every call the success would be if you got a sale, the fail would be if you didn't we're not talking about gradients of success in this case right we can't really we can't say well we got a sale of a 200 item versus a hundred dollar item we have to define it as either success or fail. So there's a lot of other scenarios where this could take place which might not be obvious as first so here's another example n equals the number of fixed trials which are going to be drives to work so we're driving to work we're going to say five times that's going to be our fixed trials the probability of success meaning success in this case we're defining as no traffic so when we're driving to work if there's traffic it's not a success if there's no traffic success now again there's no gradients here we're not saying well there's a medium amount of traffic versus a high red zone traffic versus a yellow zone traffic no we're saying either binomial traffic no traffic no traffic success traffic not a success and unfortunately there's only a 12% likelihood that we're going to have a no traffic situation so then x is going to be the number of times no traffic out of five so in other words we're trying to see what's the likelihood that we get three drives to work with no traffic out of the five drives to work which is going to be fairly low you would expect given the fact that 12% likelihood per drive that we're going to have a traffic free experience okay so if we were to do our binomial distributions x equals the number of times no traffic out of five so here is our equation now remember there's actually two of them with the binomial which is a little bit different than some of the other distributions like the poisson distribution the latest one as we talked about before is this range one which which has more flexibility but it also could be a little bit confusing sometimes so it might be easier to use since the other one is closer to the poisson the other one so we'll look at both of them so this equals binome.dist.range and then we're picking up the trials which are going to be five the probability per individual trial the 12 probability of success and then the number we're looking for is three out of the five that's what these numbers represent and that gives us our 1.34 percent the likelihood that we get three days out of five that have no traffic given the fact that each individual day we only have a 12 success rate meaning no traffic is only the 1.35 we need a new job for crying out loud or maybe move closer or something it's just ridiculous i mean i'm wasting half my day in the car i try to listen to tapes but it's just so it's hard to focus anyways the number of times so we can also do it using this formula equals the binome.dist so this is similar to the to the poisson one where we have the numbers is a similar argument up top the trials that we have similar argument but then we've got the cumulative versus non cumulative so we got the numbers we got the trials we've got the probability and then we've got this last bit over here which is cumulative or non cumulative now that allows you to say ask a question such as what's the likelihood not simply that i get three but from anywhere from like zero to three or something like that well it'll take a cumulative impact but it's more difficult to pick something in the middle right whereas the binome.dist.range allows you to have the multiple arguments on this last argument so it can allow you to do a calculation of something like in the middle like two like if i was to say what's the likelihood that i have like two to three days so i will give a little bit more of of an explanation of that here so let's imagine that we're saying that p is equal to two so i could use the binome.dist.range which has the arguments the trials the probability and the numbers the number now being two out of the five instead of the three out of the five we could use an argument of the binome.dist argument which now it has the two up front i'm just typing in the two because the order of the of the arguments are different orders but it's the same basic arguments and then the trials and then the probability and then we have whether it's cumulative or not in this case we're going to say it's not cumulative because we're looking for exactly the two so those two both of them are fairly equal in terms of the in terms of the ease of the argument and we can also if we were to plot out our data this way we can this is often a useful tool to plot out the data meaning we can say what if the chances of x are being from one up to five and then we can plot out our data and for this we're using the binome.dist.range you could use the other binome.dist as well but this range function gives you an array function which makes it a little bit easier to enter something like this because to it enter this into the system we're going to say that the number of trials that we have is going to be once again the five i made it absolute so we can copy it down to the range the probability of success is going to still be the twelve and then we have the numbers which is going to be this array that's what this stands for with the hashtag and this will just be a spill array which will give us the results so the probability that we have zero days is quite high so because remember that we had a 12 percent likelihood that we don't get traffic so it's a high likelihood that we do get traffic so that means that to have zero days of traffic free a traffic free week is not is still not it's likely that we don't get any traffic free time and so and then what if we get one day of traffic free day thirty five point nine eight percent two days of traffic freeness nine point eight one percent three days one point three four and so on if we plot this it looks something like this and if i was to compare that to to calculating just for the for p equaling two i can then say i can then find it right here right p equals two which is this nine point eight one all right if we do another one i'm going to say that this time p is going to be less than or equal to two okay less than or equal to two so we do that a couple different ways i can say all right i can look up here and go less than or equal to two meaning i'm going to include the two so i could say well it should be five fifty two point seven seven plus thirty five point nine eight plus nine point eight one ninety eight uh so we get to that that ninety eight fifty six that's the one way we can get there that's the result or we can say binom.dist.range and we enter the trials the probabilities and then uh the numbers and now i'm picking up two numbers and this is going to be the more flexibility of the.range i'm not saying it's cumulative we're we're picking up those two numbers and we could list those numbers this way if we were to list the upper and lower limits the lower limit now is zero because it's that's as far down as we can go and the upper limit is up to and including two and those and that's what's being included in this argument we could also use the binom.dist which still works pretty good because i got the numbers the trials uh the probability and then we have to have the added argument to say do you want this one number or do you want the cumulative in this case we're going to put a one which represents the cumulative which basically does the same way thing we did up top which which sums it up that way so what if we had an argument of x is less than or equal to one well if it's less than or equal to one i could use my summing format i can go up here and say all right well less than or equal to one means it would be up here of 52.77 plus 35.98 likelihood which comes out to 88.75 so we can use that method and or i can use the binom.dist.range trials probability and then the numbers and now we've got the the two numbers that we're putting in place and if i look at it in terms of my ranges here it's going to be zero goes down to zero on the lower limit and goes up to and including one and then the cumulative still works quite well here because now i can just say it's the numbers the trials the probability and then i'm going to just say it's cumulative instead of zero which is which is non cumulative so both of those seem pretty comparable but but then so now this one if i go x is greater than or equal to two well so now i've got to say okay if it's greater or equal to two now i'm going from from two down so that would be you would think 9.81 plus 1.34 plus 0.09 and then this one is too small to count so 11.2425 about and so i can do that i can do that with that summing way i can do it with a binom.dist.range the argument being the trials the probability and then i'm looking at this range which sometimes is nice to put in a table the lower bit of the range being two and then we're going from two up to five now here's where the the older one runs into is a little bit more difficult right because now if i say well what if i do that with the same format that we had with the poisson which has this cumulative argument instead of being able to put these two numbers for x right what how am i going to do that because i want to go from two up to five and the cumulative only goes from zero up so what i would have to do then is do the cumulative thing up to five and then subtract out the cumulative up to two or one whichever one i'm trying to include here so in other words what am i including it's it's uh greater than or equal to two so greater than or equal to two so that means the lower limit uh is two and the upper limit is going to be five two is going to be included so you have to do something like this if you wanted to just calculate it with that old binom.dist you'd have to say okay binom.dist numbers trials probability and then cumulative minus binom.dist to subtract out that first half of the curve uh and you got and so this is where it gets a little bit more messy and this is where that range function or one area that range function works well let's do one more here and then we'll take a look at another format uh hold on i deleted the wrong thing let's see let's delete this all right so now it needs to be x is just greater than two not greater than or equal to just to notice that subtlety here because now we're saying it's not the lower limit is at three because we're not including the two so from three up to five so if i did that up here i can say okay so it's greater than two but not equaling two so that means it's going to be the 1.34 plus 0.09 or 1.43 about and i can do that by adding it up i can do that by the binom.dist.range where the last argument are going to be the last two arguments are the three and the five or if i use the old format again it's a little bit long because i'd have to say binom.dist up to the five minus the binom.dist to to take out the first bit up to up to and including the one but not the two right so it gets a little bit messy so we do that in excel if you want to practice doing that in excel note also the the binom.dist.range also has that spill that spill capacity so if you were to set up your lower and upper ranges for a set of questions like this then it becomes quite easy to use the spill feature in a binom.dist.range as opposed to the to the cumulative because you can then set up your lowers and your uppers you can go binom.dist.range put in your trials your probabilities and then and then the numbers are going to be these two ranges so now i just for the numbers instead of having two and two and zero and two i select this range and then this range and that will then spill down the results so that's another reason why this binom.dist.range is a little bit more flexible but oftentimes to me i like to build something like this and and and you can do this fairly easily with either of the the functions so so that's another example and just to note that this example might not be as intuitive right but but because traffic example versus a coin flip probably comes to mind when you're talking about a binomial kind of thing at least it does for me but there's a lot of things that you might be able to put you know that you might actually be able to put into a category of success or non-success and have a similar type of scenarios