Welcome back to an intuitive introduction to probability, decision making and uncertain world. In the last lecture, we started playing around with the NORM.DIST and the NORM.S.DIST function in Excel to calculate some normal probabilities. Let's continue. First, and step by step solve more and more difficult problems. Here's now next question we may ask. What is the probability that we get an X in a range of variables. So, here, what's the probability that an X shows up in this yellow range between A and B. This area underneath the curve. So, back to the. You remember our construction company in Florida, and now he has a new question. What is the probability that a physical parameter X, Ends up, at a particular pillar, between 0.7 and 1. So I no longer want the probability just below 1, No I probabilities deranged between 0.7 and 1. Easy now, because we can handle the NOR.DISC function. <i>The probability that X falls between 0.7 and one...</i> <i>is the difference of cumulative probabilities.</i> <i>So I do the cumulative probability, add one</i> <i>minus the cumulative probability at 0.7.</i> <i>I use these 2 NORM.DIST commands and voila!</i> <i>About 82%, or to be more precise, 81.86%.</i> <i>So the area underneath the curve between 0.7 and 1</i> <i>of our normal distribution that we're using here for theory,</i> <i>that area is 81.86%.</i> Remember the standard normal. I could now transform the 0.7 into a -1 Z value. So that one, into a two Z value, and then use a NORM.S.DIST function. And that's what I've done here, same answer of course. I don't need to do this, but I could go this detour via the standard normal. Now, many people if they remember something from the normal distribution, then it's this + -1 σ, + -2 σ and so on probability. This is what we see here. <i>Every normal distribution has these properties.</i> <i>The area underneath the curve, but around the mean.</i> <i>Between mean, -1 standard deviation. And mean, +1 standard deviation.</i> <i>It's 68.27%. </i> <i>+ - 2 standard deviations. That's 95.45%.</i> <i>+ - 3 standard deviation. 99.73%.</i> <i>Every normal distribution has these properties.</i> Finally, there's one last calculation we need to learn about. Which is the inverse of calculation. Let's start with the following questions. Theory doesn't like a 2.3 failure probability. That's way too high. Let's now say, they want a failure probability of only 0.1 percent Put differently, they want a 99.95% probability of producing big pillar. Where will this cut off X have to be? If they could decide on this cut off X, between good pillar and bad pillar, where should that be? Notice now, this question is different than the previous questions. Previously, one or two values for X were given, and we had to calculate a probability. Now, we want to go the other way around. Probabilities have been specified, 0.999 good. 0.1% bad. And now we want to go back and find an X that goes with these probabilities. So, here down here, we want a NORM.DIST of this unknown number for our distribution with mean point 8, and standard deviation point, 1 to be equal to 0.999. Now I know some excel visitors will say oh, that's a GOAL SEEK function or SOLVER function that I could use here. Yes, but it's very more elegant to do it using the NORM.INV function. How cool is that? Excel gives us a function NORM.INV, 'inv' for inverse that gives us this value X when we specify the probability. So, if we type in NORM.INV of this probability P, <i>and of course I have to give excel the mean μ, and standard deviation σ</i> <i>then it spits out the value Xp.</i> <i>So that to the left of Xp, I have the probability P.</i> <i>And tot he right of Xp, I have the probability 1 - p.</i> <i>Maybe some of you have heard the name percentile, </i> <i>or quantile of a distribution.</i> <i>That's exactly what we're computing here.</i> <i>So, here's an illustration.</i> <i>I always think of, I have a probability P</i> <i>that I put in the left tail of the distribution.</i> <i>I put the probability P in, and at some point I run out of probability.</i> <i>At that point, I get the value Xp.</i> In the theory example, I would now type in NORM.INV of 0.999. The mean point 8, σ point 1, and voila I get the number 1.109. Now what does it mean? In our application, this has the following interpretation. If the cut off is 1.109, then there's 99.9% probability to the left. In theory, that means there's a 99.9% chance of a pillar having an X less than 1.109 of P now, under this new regulation, being a good pillar. And there's only a 0.1% chance of being above this cut off. We can check this and we get the number. And using NORM.DIST I can now go back and check. There's a tiny rounding arrow here but other than that, with respect to 3 digits I get exactly my 0.999. Here's a summary of all the excel functions that we may need. There's a NORM,DIST function and the NORM.S.DIST function we saw in the previous lecture. There's a NORM.INV function that we saw in this lecture. Actually that was an inverse for the standard normal. And those are the 4 functions that we can use in excel. And with those 4 functions, I can calculate any probability or any inverse value that I want. And in the last lecture of this module, I will show you another cool application of these functions in action. This wraps up this lecture, we learned more about calculations for the normal distribution. Most importantly, we learned the inverse of the normal distribution and the corresponding function in excel. Thanks for your attention. Please come back for more.