 Statistics and Excel, Exponential Distribution in Seconds, Roller Coaster Line 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 Accounting Rocks product line, if you're not crunching cords using Excel, you're doing it wrong. A must-have product, because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement, the obligation, a duty to share the tools necessary to properly channel the creative muse. And the muse, she rarely speaks more clearly than through the beautiful symmetry of spreadsheets. So get the shirt, because the creative muse, she could use a new pair of shoes. 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. Blank tab, blank worksheet, except for the image here, but you don't really need that, 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 what we will be doing. We're looking at an exponential distribution this time, often it being related to a Poisson distribution. In prior presentations, both often having to do with real-life practical examples in line-waiting situations or sometimes over some space such as the example we had with the Poisson distribution was how many potholes in a 100-mile space of road, for example. We're going to start here with the line-waiting situation. Our example was with a roller coaster ride, so we will be sticking with that. We're going to be calculating the Poisson distribution again so we get an idea of the question being asked with a Poisson distribution so we can then compare it to the questions being asked with an exponential distribution. The Poisson distribution asking questions such as what's the likelihood of three people arriving within some time span, and that's going to give us the percent likelihood that those arrivals will happen. So we're looking at the numbers of arrivals. Whereas when we're looking at an exponential situation, we're asking questions such as what's the likelihood of so much time passing, three, for example, seconds, or if we were talking in minutes, what's the likelihood that so much time passes between arrivals? So you can see kind of the relationship between these two things and two different questions. We're kind of flipping around the question from the Poisson to the exponential, oftentimes, in these line-waiting type examples. Let's go to the blank tab and start building this out. I'm going to move my roller coaster down. I'm going to format the entire sheet as we always do generally unless I forget, which sometimes happens. Let's hit the triangle right-click. We're going to format this thing. We're going to make it currency, negative numbers bracketed and red, no dollar sign, no decimal. I'm going to say OK, and then I'm going to embold the entire thing, home tab, font group, and have it emboldened. You might not need to do that. You don't need to if you don't like to be too bold. But when we're recording like we're on camera, you've got to be bold when we're doing the filming here. In this case, we'll start off, we're going to say X is going to be equal to the arrivals during one minute. And then we're going to say that the mean is going to be equal to the average 3.25. I'm going to format that, home tab, numbers, and add some decimals. So we're going to say within one minute time span in our line here, we got 3.25 people arriving on average in a one minute time span. So now let's then say OK, well then what's going to be the mean minutes between arrivals? I'm going to make column A a little bit larger by double clicking between A and B. So we're going to say, all right, now I'm looking for the, what's the time between arrivals then? Well, if 3.25 people on average are arriving within the span of one minute, I could say then this is going to be equal to 1 divided by the 3.25. We're going to have to add some decimals there, home tab, number group, adding some decimals. So we've got the average, in essence, mean minutes between arrivals are 0.30769. So about, you know, 0.3 minutes between arrivals on average, right? Because now we're looking at the intervals between. So then you have the question on the, in this particular case, now the question is, well, should I be doing this calculation in minutes? Or would it be make more sense to then drop down to seconds? So we have this measuring tool kind of question, what do we want to be measuring this in? In this case, it looks like seconds might be the better way to go. So if I needed to convert this to seconds and do my calculations in seconds, I would have to say, okay, the mean seconds, I hit caps lock somewhere and seconds between arrivals is going to be equal to the minutes times 60, because 60 seconds in a minute. Let's add some decimals. I'm going to decimalize it. And so notice that's not a perfectly rounded number. But if we use that number in Excel, then I can use that cell and it will use the actual number that has been calculated here. So we'll keep that and then I can say, okay, X now X is going to be equal to now the seconds between arrivals. So if X is equal now to the seconds between arrivals, we're going to say P, for example, of X is we can ask questions like this X is less than 60. So 60 seconds, one minute, we're less than a minute. These are the kind of questions that we might ask. And we can use our trusty formula now, which is expo.dist. So let's put that in here, we're going to say this is going to be equal to expo.dist. And I'm going to say that X is now going to be 60. I'm just going to hard code that and we're going to say comma. Now lambda, lambda is going to be one over the mean seconds between arrivals, which we calculated here at the 18.46. So that's going to be lambda. And then we have this similar question of is this going to be cumulative? Or is this going to be as of one point? So we're not just looking at 60. We're saying it's going to be less than 60. So I'm going to say cumulative and I'm going to pretend that was less than or equal to. I should have an equal there so that we include the 60. So it's less than 60 or equal to 60 or so like less than a minute. So we have true is the cumulative, false would be the non cumulative. So I can type in true because we want the cumulative or a one and then close it up and enter. And then let's go with the percent to find it. And so an add a couple of decimals. I'm going to double click in here and say less than or equal to 60. So in this case, high probability because and that's why we put this particular problem in seconds. Because, you know, there's a high probability that that 96.12% probability within the one minute time frame. So let's go ahead and graph this out now or make our table of it. I'm going to make C smaller. I'm going to say first let's think about our Poisson distribution and then we will do the exponential. So I'm going to first think of Poisson where I say X is going to be equal to I'll say the arrivals during one minute. And I'm going to wrap the text on that home tab alignment wrap the text. Let's center it and put an E on minute. And then we're going to say P of X is going to for the Poisson function. And I'll say this is going to be home tab alignment wrap the text and center it again. Let's make these black and white home tab font group black and white. So there we have it. And then I can say the number of rows. And again, I could do this thing with the rows and say the number of rows for Poisson. I'm going to draw from this cell so I can use a function to do it. And I'm going to say what did I have in my example to let's say 29 rows. And so I'm going to then instead of doing one or starting at 012 and copying it down to 29 like that, I'm going to use our sequence so we can practice using the sequence spill function. So there it is. Then the number of rows that we want is going to be this 29 plus one because I want to have a zero in it as well, comma. I don't want any columns. So I'm going to two commas to get to the next argument started at zero close it up and enter and it spills out 29 which I can now adjust by simply adjusting this number to like four goes up to four to 29. So that's pretty neat. And then I can do the Poisson distribution. So now I'm going to say the Poisson distribution equals Poisson dot dist tab. And we're going to say that X is going to be equal. Now this time I'm going to try to have the Poisson distribution spill as well. So I'm going to select zero hold down control shift down. So I select the whole thing control backspace to get back up to the top comma the mean we're talking about this mean now the 3.25. I'm going to select F4 and the keyboard so that's absolute dollar sign before the B and the two comma. We don't want it to be cumulative because I'm looking at the result for each number for each X on the left. So I want to type in false or I can put a zero in. I'll put a zero in and then boom and it spills it down. I'll select the whole column here and percentify it. Home tab number group percentify so we could recognize and then add some decimals. And so there we have our Poisson distribution. So we're basically saying what's the likelihood that zero people show up within the one minute time frame. 3.88 what's the likelihood that one person shows up within the within the one minute time frame 12.60. What's the likelihood that two people show up 12.48 within the one minute time frame. What's the likelihood that either zero to three up to two people and including two people show up. Well here I can actually sum these up and get get the general response. So I can say this is like equal to the sum of here. Now we might not be able to do that with with the exponential distribution. You got to kind of be careful there. But that's the general that's the general questions that we can ask with it. Now let's do a similar thing with the exponential. So I'm going to make column F smaller and this one is going to be equal. This time I'm going to do it this way equals the X is the seconds between arrivals. And then this is going to be P of X for the X-Pone. I'll call it. And let's make this one home tab font group black white wrapping the text and centering it. And then I'm going to bring this one down to like I don't know 120. Let's say seconds because we're talking seconds. So let's say this is rows for exponential distribution 120. So we'll do our same kind of thing with a sequence equals sequence. Boom. And then the rows that I want. I'm going to say our 120 from that cell plus one so I can put a zero on top comma. And then we want to have two commas because I don't need columns. And then I'm going to start at zero close it up spills it down. And this one doesn't shouldn't be a percent. This should just be normal. Let's see if I can just format paint this one up top. I don't know what percentified that one. But there we have it. Now we can do our X-Pone for each of these. So I'm going to say OK this is going to be the X-Pone dot dist. And then X I'm going to try to do to spill this out again. So I'm going to select this cell and then a hold down control shift down arrow. Oh K-Paw so I went to the right arrow. Let me do that again. Let's go here control shift down arrow and then control backspace and then comma and then lambda. We had over here we calculated lambda to be I'm sorry it's one over this number that we calculated on the right. I'm going to say F4 on the keyboard to make sure that that doesn't move down when I copy it down. So dollar sign before the B and the five and then comma. Then we want the this time not to be cumulative. So I type in false or I can put a zero and spills it down. Let's add some decimals or make it a percent possibly home tab number group percentified adding decimals. So there we have it. So now this is asking the question of what's the likelihood that like one second passes between or zero seconds or one second passes between between arrivals. Right. So now we're trying to think about how long is it going to take between arrivals. What's the likelihood of two seconds happening between arrivals. Now note the question we had down here P of X is less than or equal to 60. So you would think that I can add this whole thing up like this equals the sum of this down to 60. But if I decimalize that we don't get exactly the same thing possibly due to the curvature of of the graph here. So you got to be a little bit careful with the exponential in that case. So you want to so if I wanted to do that I could I could get the exact area under the curve by doing my cumulative type of calculation. Let's add our two graphs with this. I'm going to select let's select this one and make the poisson chart so we can see what it looks like that we've seen in the past. We're going to go into the insert. We're going to go into the charts and hit the chart dropdown. Let's just make the good old bar chart. And we're going to say there it is. I'm going to select the data like we typically do. And then I'm going to say let's pick up our X numbers and there we have it. So there we have it. So so you recall the general character of the poisson. Now let's do the exponential. So we could do the exponential with a with a similar graph if we wanted to. I can go all the way down here and say let's do the same graph. We'll say insert charts graph and we get something like this. And let's change our X again and say I want to change this and make sure we pick up the zero. Yo pick up the zero yo and then we're going to say there it is. You might also use something like this just to fancy up the graph. You can say let's go to the insert line graph and let's pick one like like this one. So it fills in like so because that's fancy. So we could do something like that if we if we want to. Why not man no one's stopping you if you want to get fancy. So then we're going to say I'll get fancy if I want to get fancy. So I'm talking about so then there we have it. So we have we have something like that so that's the typical that's you know the look of the curve that you would generally imagine when you're thinking of an exponential type of situation. So in this case what's the likelihood that that someone arrives within within. What's the likelihood that two second that one second passes between arrivals five point one three two seconds past four point eight six and you could see the drop happening quite sharply here as is being represented by the graph. That's what you would basically expect to see on the graph. Now we'll do a similar calculation next time but we'll try to think about it in terms of wait times for example in minutes rather than a situation in seconds but you got to be careful about you know obviously the conversions between minutes and seconds. So let's make this blue and bold home tab font group let's make it blue here and let's border it just to make it a little bit more as our typical formatting home tab font group border blue here. Let's make this one border blue to border blue and then let's check the spelling if we could expo that's that's cool I don't care about that ignore that one during during you would think that's dist whatever ignore that. Okay that's pretty good on the spelling I feel like.