ISACA COBIT 5 – Measure (BOK V) Part 12

39. Normal Distribution Calculations Using MS Excel

So here I have my Microsoft Excel opened. And now I want to find out what is the probability greater than z is equal to one two. So for that, let’s click on any of these cells and put the formula. And for formula you start with equal to and then you put norms this norms dist. So this is the one which you are looking for norms this. And you put the value of z and value of z was one point twelveteen in our previous example. So let’s put that and press enter. So this will give you . 8686.

What does that mean? So let’s look at that. So if here if I draw the normal distribution curve then this value which was 1. 13 which will be somewhere here. Because this is where z is equal to zero. Because that’s a mean. And this will be something like z is equal to 1. 2. And this 86. 86. This is this area on the right. So the area on this side will be one -0. 868,643 so if you want to find exact value here. So maybe what you can do is go there and put value as one minus that. So what I want to do here is I go and change this is equal to one minus list. And this will give me 13. 13%. And this is exactly what we calculated.

So let me redraw that normal distribution curve here. Now, if this is z is equal to zero. And when you say z is equal to 1. 1 point twelve, then this area will be 13. 13% 13. 13%. So that’s how you can calculate the probability. And if you know the probability, then you can find the z value using norms I and v. Let’s do that. So let’s say if you want to find out the z value where probability is 75% and 75% from the right side. So let’s look at that and then I will explain using the chart.

So equal to norms I and v, norms I and v. And I’m looking at 25% chance. So 25% chance here is zero point 25. And I need to find out the value of z for this. And z for this comes out to be minus zero 67. So how does that look like? Let’s look at that. So this z value is coming out to be in the minus side and which is minus. And this is my z is equal to zero. And if this is z is equal to one, then this will be somewhere you will have z is equal to minus zero point 67. Four four nine. And this was z is equal to minus one. This was z is equal to minus one here z is equal to plus one. So that’s what if I draw a line here, then this is the area which represents 25%. So if you know the probability, you can find out the z value. And if you know the z value, you can find out the probability using Microsoft Excel.

40. Normal Distribution Calculations Using SigmaXL

So earlier we used Microsoft Excel to find out z values and probabilities or the probability related to a specific z value if you are using some software. Let’s take an example of Sigma Excel here. Let’s see how that thing is done using Sigma XL. So for that, I open my Sigma XL here and then I’m looking at templates and calculators. So click on that and in that you see probability distribution calculators.

So you have calculators for various distributions. And here we are talking about normal distribution. So I click on normal distribution. Here is the calculator. So these yellow place, that’s where you put your inputs and your output will be at the bottom. So let’s say earlier we said that plus minus one sigma gives us around 68% of the area. So let’s do that. Let’s keep mu is equal to zero and sigma is equal to one because we are looking at standard normal distribution and x one and x two are two values of z. So let’s say x one is minus one sigma and x two is plus one sigma.

So we want to find out what is the area between plus minus one sigma. And this comes out to be here. So if you look at this, the first one, which tells you that between x one and x two there is 68. 26 89% area. And if you want to find out area outside this zone, that will be the second one. So this table tells you various things related to plus minus one sigma. So you can look at the appropriate picture and find out the area. So, if you want to find out the area which is below minus one sigma, then you can look at this third one. The third picture shows the area below minus one sigma and that comes out to be 15. 86%. So you have various options here, you can appropriately pick the value and this is how you find out the probability of normal distribution curve using Sigma XL.

41. Binomial Distribution

Earlier we talked about normal probability distribution. That was a continuous probability distribution. And as you remember, data could be either continuous or discrete. So we talked about continuous and in continuous we talked about normal probability distribution. We did not talk about students Ttest, chi square and F distribution. We will do that later. But now let’s move on to some of discrete distribution. And before we do that, let’s understand once again the difference between the continuous and discrete data. Let’s do that on the next slide. So here we have the difference between continuous and discrete variables. So the difference here is if a variable can take any value between two specified values, it is called as continuous variable. Otherwise this is known as discrete variable. And continuous variable, as you would know, is measurement, height, weight, length, all those things are continuous variables, whereas discretes are counts. How many times you get a head when you flip a coin that is discrete variable. Now on the next slide, let’s talk about discrete distributions.

What are the probability distributions when your data is discrete? Let’s see that for discrete data we have two commonly used distributions binomial and poisone. But in addition to binomial and poisone, there are a few other distributions also, which we will be looking at, which are listed here. And these are Bernoulli Hypergeometric Geometric and Negative Geometric. We will look at these as well in this session. Let’s start with binomial probability distribution on the next slide. So earlier when we talked about normal distribution, we looked at the property of normal distribution. What are the properties of that? We said that normal distribution is a bell shaped, normal distribution is a symmetric. So let’s say if this was the normal distribution, we talked about that, that normal distribution is a symmetric, it’s a bell shaped, its area is divided into two halves. So those were properties of a normal distribution. Now we are talking of binomial distribution. What are the properties of a binomial distribution? Since the binomial distribution is a discrete distribution, so the variable here is discrete, not the continuous. So you won’t get that distribution something which is smooth like you saw in normal distribution you will get the binomial distribution which will be looking like some sort of a steps, steps like this. So discrete distribution will have steps will not be a continuous flow, just like what you had in normal distribution. So the properties of binomial distribution are that this consists of experiments and which has N repeated trials. The simplest example to understand this would be flipping a coin. So you flip a coin, you get either a head or tail.

That is the simplest example to understand binomial probability distribution. And that’s what we will be doing here, taking that as an example to understand binomial distribution. So this has N repeated trials. That means you flip the coin n number of times n could be five and could be ten. So if n is ten. You flip the coin ten number of times so that’s one property of binomial distribution and each trial can result in two possible outcomes. Just like in flipping a coin you get two outcomes head or tail. When you are doing inspection and you pick an item you have two outcomes there accept the article or reject the article. Accept the piece or reject the piece. There are two outcomes. So all binomial distribution will have two outcomes and one is called as success. The one outcome is called as success and another is called as failure. In flipping of a coin if you are interested in knowing the number of heads then you can say head as a success. If you are interested in knowing the tail then you can say tail as a success. If you have a village and you randomly select a person whether the person is a male or a female, that’s where binamal distribution can be used.

Because there are two outcomes here if you select a person, person is either male or a female. If you are interested in finding out what is the probability of selecting a female then the female selection would be considered as success. And the third property here is that the probability of success is denoted by P. Small p is the probability of success. So in case of flipping a coin the probability of success would be 0. 5 and every trial will have same probability of success. So the probability of success will not change and trials are independent. That is outcome of one trial doesn’t affect the other trial. So here if we take example of five men male and five female in a group and we are selecting one person, the chance of getting one person selected as a female would be five by ten. Now if we remove that person, that female out of the group. Now you have four females in the group. Now if we select the second person the probability of selection has changed.

Now the probability of selecting a female would be four by nine. So this is not something which is possible in binomial distribution. In binomial distribution this probability has to remain the same and trial has to be independent. The second trial should not be affected by the first trial. So going back to our simple example of flipping a coin and if I have to draw probability distribution for that if I flip one coin it can be either head or tail. So there is a zero five probability of getting head and zero five probability of getting tail. So if I have to draw the distribution for that, that will be something like this. So head is zero five and tail is zero five and here I have 0. 5.

So this is if I flip one coin and if I flip two coins then what are the probabilities? Then either I can get both head or I can get the first head the second tail, first tail, second head and both tails so if I am interested in knowing about number of heads, how many heads would I get if I flip two coins? So here I get two heads. In these two cases, I get one head, and here I get zero head. So if I draw probability distribution of that, this will be zero and this will be one four. There’s one fourth chance of getting zero heads. There is one half possibility of getting one head, and there is one quarter or one fourth probability of getting two heads. So this is what my probability distribution will look like. And what is the formula for binomial distribution? How do we find out probability of something that is defined by this formula? The probability of getting x number of successes is defined by NCX multiplied by P to the power x multiplied by one minus P to the power N minus x. We will talk about each of these variables, what these are.

So, X, here is the number of successes resulting from Binomial experiment. So let’s say if our experiment is flipping a coin ten times, so you flip a coin ten times and you want to see the probability of getting one head only in those ten flipping of coins, you understand that that probability will be low. Because if you flip a coin ten times, there is a good chance that you will get five heads and five tails. But there is a possibility that you might get one head and nine tails. So what is the probability of getting one head when you flip coin ten times? So here number of successes resulting from this is one. So we are looking at one head in flipping of coin ten times. Number of trials here is ten because we are flipping the coin ten times. Probability of success in our case is getting head either head or tail. The probability is 0. 5 when you flip a coin and Q is the probability of opposite of P. So probability of failure and which is one minus P, which is also equal to zero five. And in this formula here you see one minus P. This you can even write Q also somewhere you might see Q also because one minus P is equal to Q. So coming back here, n factorial and n is ten. So n factorial would be ten factorial.

And just to recall that ten factorial is equal to ten multiplied by nine multiplied by eight till we reach one. And PX. Is the binomial probability? This is what we want to find out. What is the probability of getting one head when we flip the coin ten times? So NCX is n number of things taken x at a time. So this is what we have already known, that N in our example is ten and X here is one. So we will be having ten C one in our case. So if we put all these values here, we can find out the probability of getting one head when we flip coin ten times. So this will be NCX, which will be ten C. One multiplied by PP is equal to zero five to the power of x and x. Here is one head. The next one is one minus P, which is also zero five. One minus zero five is equal to zero five. And to the power n minus X. N minus X is ten minus one is equal to nine. And this is something which you can calculate and find out the value using a calculator. So this is how you use binomial distribution to find out the probability. This slide provides the summary of whatever we have discussed earlier.

So we talked about binomial probability is the probability of getting exactly x successes. And we explained that with the help of an example where we flipped a coin ten times and we were looking for the probability of getting one head? Now, if you get a question which is slightly twisted, for example, if you flip a coin ten times, what is the probability of getting two or less number of heads? So to calculate that, what you can do is you can find out the probability of getting zero heads. So which will be p zero exactly the way which we did for p one last slide plus p one plus p two. So you need to calculate these three probabilities and if you add that, that will give you the probability of getting two or less heads when you flip a coin ten times in the previous example. So another thing is this is the formula and we talked about that earlier in the previous slide and we also talked about NCX. This can be expanded to factorial n divided by factorial x and N minus x factorial. So this is we have here, or just to put in a better way, I would say n CX is equal to factorial n divided by factorial x and factorial n minus x. So this summarizes whatever we have discussed earlier. So earlier we talked about finding out the probability of an event in binomial distribution.

Now, here on this slide, let’s look at how to find out the mean, variance and standard deviation of binomial distribution. Let’s take the old example of flipping a coin ten times and looking for head and we were looking at finding out the probability of getting one head there. Now, what is the mean of this distribution? Mean of this distribution is given by N multiplied by P. Here, N was number of trials. So in our example, number of trials were ten. And the probability of success, which was getting head was 0. 5. So ten multiplied by 0. 5 is equal to five. So five is the mean of the distribution. And if you just look at with a common sense when you flip a coin ten times each time you get a half chance of that, then you can understand that there is a good chance that you will get five heads. If you flip the coin ten times, you will get five heads. So there’s a good chance of that.

And that is the average of this distribution. And that’s how you find out the mean of binomial probability distribution. How do you find out variance? Variance is given by N multiplied by P multiplied by one minus P. And as I earlier told you, this also can be written as Q. So N multiplied by p multiplied by q, n is ten. In our case, probability of success is 0. 5, that is for getting head and for not getting head. Getting tail is also 0. 5. So this gives us zero five and zero five makes it 00:25. Multiply by ten is equal to 2. 5. So that is the variance or the sigma square and the sigma will be square root of 2. 5. You can calculate this value and that’s how you find out the mean variance and standard deviation in case of binomial distribution. Here on this slide, it is a sort of a summary whatever we have discussed. But this lists down five condition for binomial distribution.

So for a distribution to be called as binomial distribution, there has to be fixed number of identical trials. So in flipping a coin ten times, you have a ten trials and each trial is identical. So that’s condition number one for binomial distribution. And for each trial there are only two outcomes success or failure. You cannot have more than two outcomes and the probability of success remains the same for all the trials. In our case, as many number of times you flip the coin, the probability of getting head will be zero five only. That doesn’t change with each trial. And each trial is independent of each other. The one trial is not affected by the previous trial and x is equal to number of successes observed for N trials. In our case, we were looking for one number of success. So these are the five conditions or the five things which you need to remember for binomial distribution. There are certain variation to binomial distribution, which we will be talking just after this session on binomial.

42. Bernoulli, Hypergeometric, Geometric and Negative Geometric Distributions

So after talking about binomial distribution, now let us look at some of the variations of binomial distribution. The first one here is Bernoulli distribution. If you remember, in binomial distribution, we took an example of flipping coin ten times and looking for number of heads. Each time you flip a coin, that was Bernoulli trial. So in binomial distribution, you have number of trials and each trial is Bernoulli trial. And the distribution of that is Bernoulli distribution. So distribution of success is in a single trial. We just have one trial here, unlike in binomial where we had number of trials and we were looking for a fixed number of successes. So the simple example of that is what is the probability of getting ahead in tossing a coin once? So we do only one experiment in Bernoulli distribution. Another example could be if you have a lot of pieces here you have 1000 pieces in a box, two of them are defective. What is the probability of getting a defective piece when you pick one random piece out of this box? So two defective 998. Okay? So what is the probability of getting a piece which is defective? So that will be your Bernoulli distribution. So let’s look at another variation of binomial distribution. And this is hypergeometric distribution. Just like binomial distribution here in this case, which is hypergeometric. Here also we have a fixed number of trials. Just like earlier, we had the ten times we flipped the coin, we had ten trials. In hypergeometric, also you have a fixed number of trials. And here also you have a two possible outcomes only.

So here also you have two possible outcomes only, success or failure. But what you don’t have here is that in binomial distribution, the probability was same for all trials and each trial was independent. This is not the case here, the probability of each trial is not the same. When you flip a coin every time, the probability of getting head was same, which was zero five. But if that is not the same, then you need hyper geometric distribution. And what that case could be? That case could be when you are taking a sample without replacement. If you take a sample without replacement, then each time you take a sample, your lot gets affected. So for example, if you had a box which had five good items and five defective items. So if you draw one item and you are looking at defective items as a success. So if you draw one item and that item was, let’s say, defective, then you are left with nine items in this box and you are left with five good and four defective.

So after drawing one defective, if the first draw was defective, then you will be left with five good and four defective items in that. Now the chance of getting a defective item has reduced. Earlier, this was five by ten. Now this is four by nine. So anytime when you have a population which is finite and you are looking for without replacement. So without replacement your lot gets affected, your probability gets affected, your trials are not independent of each other. That is where you use hypergeometric distribution. And in this example, when you have five good and five defective pieces, each time you draw a sample, your probability of the next draw gets changed, unlike binomial. And that is where you use hypergeometric distribution. So if you see this formula, this was for binomial distribution in hypergeometric you will not have this term p because there is no fixed probability of success here. So you won’t get this term. What is the formula for getting the probability for hypergeometric distribution? We will look at that later. But this is for the binomial which is not applicable when you draw items from a finite lot without replacement.

And when I say finite lot, because finite lot gets affected much more than infinite lot. Let’s say instead of five good and five defective is if this lot was 5 million good and 5 million defective items in that. Now if you draw one item, whether that’s good or defective, the next draw will hardly change. Whereas in this case this has changed a lot. The probability has changed a lot when you add a finite lot. But when your lot is infinite or much bigger, then the effect because of selecting one item, the effect of that on other will be very minimum. So in those cases when you have a bigger lot, much bigger lot, you can still use binomial. But when you have a finite lot and when you are drawing the sample without replacement, go for hyper geometric. So in case of hyper geometric distribution, this is the formula to get the probability distribution which is here.

And as you would see that this does not include the term p, the probability of success, probability of failure, because that is not a constant in case of a finite size where the sample is drawn without replacement. So what you have is N is the size of the population, how many pieces you have. In our case we said that five good, five defective, so we had N as a ten here. In that case, number of successes in population is already known.

So if we are looking for defective items then getting defective is a success. So number of successes, we know that these are five, which in this case where we have five defective items in the box and number of successes that result from experiment. So here we can find out what is the probability of getting two defective when we draw five items from the box. So if we draw five items from the box, then five is the N, these are the one without replacement. And if we are looking for two defectives out of five, then two is this x and p x is the probability of getting exactly x successes. Exactly x successes. This was the same in case of binomial also. But the only difference, as I’ve already talked about that the only difference is here the probability doesn’t remain constant because we are drawing from finite population without replacement. So here we have an example of Hyper Geometric distribution here. In this example, if there are ten people which has six male and four female, and three people are selected without replacement, what is the chance that two of them are female?

So if two of them are female, then the chance of that is P two probability of getting two females is four c two, which is here, because the A here is number of successes in the lot, which is four females. And we are looking at two females in the sample when we draw three from the lot and capital N here is ten, because there is a ten total number of people. Small N is three because we are drawing a sample of three people from that group of ten people. So if you put all these values, you can get this, you can calculate that using a statistical calculator and in Excel you can do the same thing using this function, which is Hype hyper Geometric distribution function. And with that you can calculate the probability. And we earlier had talked that we use hypergeometric distribution when we have a finite lot and we are drawing without replacement.

But if you are drawing a small number of items, let’s say if you have a box in this, you have 100 items and you are just drawing five items. From that the effect of each item being drawn, the change of probability because of that will be very very small. So we have a sort of a thumb rule which says that when the sample size is less than 5%, if you want, you can use binomial distribution instead of going for Hyper Geometric, because the effect of drawing a sample without replacement will be minimum in this case when your sample size is less than 5%. So earlier when we talked about binomial distribution, in binomial distribution we were doing a fixed number of trials. So for example, we were tossing the coin ten number of times and we were looking for specific number of successes. So we flipped coin ten times and we were looking for one head coming in that.

So that was something which we were doing in binomial distribution. Whereas in geometric distribution here we are looking for number of trials needed to get the first success, how many trials we need to do to get the first success and for example, what is the probability that the coin tossed repeatedly? So if I keep on tossing the coin, the first head appears on the fifth trial. So this is what we are looking for when we think of geometric distribution. So I hope the difference between geometric and binomial is clear. Here in binomial the number of successes were something which we were looking for number of successes. For example heads. How many heads? One heads in ten trial. Here in geometric we are looking for number of trials, not the number of successes. So that is the difference between binomial and geometric distribution. So the next distribution is negative binomial distribution. Negative binomial distribution is the generalization of geometric distribution which we talked earlier. In geometric distribution we talked about number of trials needed to get the first success. In which we talked about what is the probability that if the coin is tossed repeatedly the first time head appears on fifth trial. But there we were talking about the first time head. If I generalize that second time head, third time head comes in five trials, that would be my negative binomial distribution. In geometric distribution we were looking at the first event to happen, first success to happen. In negative binomial distribution we are looking at number of successes to happen in number of trials.

And if I look at the difference between negative binomial and binomial, the focus is different. In binomial we had a fixed number of trials. We had five trials, ten trials. And then we were looking at number of successes. In ten trials, how many times I get the head? That was something which we were looking in binomial distribution. Whereas in negative binomial distribution our focus is on number of successes. Here the number of successes are fixed, not the number of trials. So here the example would be what is the probability if the coin is tossed repeatedly third time head appears in fifth trial. So with this we complete our discussion on the distributions which are related to binomial distribution.

• Category: Uncategorized