When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Finding probability in a normal distribution with norm.dist. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "In this video, we'll use Excel's norm. dist function to find the probability of an area under the standard normal curve. Suppose we know the mean and standard deviation of home prices for homes that sold in a small U. S. city last year and we also believe the population of selling prices is normally distributed. Without access to any other data, we can use norm. dist to find the percentage of homes which sold for greater than any value, for less than any value, or between any two selling prices. Norm. dist requires four pieces of information. I've pasted in an image of the dialogue box."
A screenshot of the Function Arguments dialog box contains four text fields: X, Mean, Standard dev, and Cumulative. Text is overlaid on each field: X, Value of continuous variable; Mean, Population mean; Standard dev, Population std. dev.; cumulative, true or false.
Narrator: "Let's walk through each required component. First, the X is a specified value for a continuous variable. In the first question we need to answer, X is $150,000. Next, the mean, which is our population mean, has been given to us as $221,100. Third, the population standard deviation has been given as well, and that's $47,110. And finally, are we seeking a cumulative or a noncumulative value? True returns the cumulative probability up to and including some value. False will return the height of that X value on a probability density function."
Text: P left parenthesis x equals a value right parenthesis is effectively zero.
Narrator: "On an interesting note, given a continuous probability distribution, the probability of equaling any one value is effectively zero because we have infinite possible values. Therefore, the probability of X less than or equal to 150,000 is the same as the probability of X less than 150,000."
Text: P left parenthesis x less than or equal to a value right parenthesis equals P left parenthesis x less than a value right parenthesis.
Narrator: "Let's use the dialogue box to answer the first question. In the cell, I'll type equals norm. dist, open parenthesis and then click this fx button to open the dialogue box."
Equals Norm dost dist left parenthesis is entered into cell E11. The F x button, left of the formula bar above the sheet, is clicked to open the Function Arguments dialog box.
Narrator: "In the dialogue box, we'll enter 150,000 with no commas."
One-hundred-and-fifty-thousand is entered to the X text field.
Narrator: "For the mean, I'm going to go up here and click on the cell that has my mean. And for the standard deviation, I'll do the same. And then, for cumulative, I'll say True. We can see the answer is already showing up. It's about 6. 5%. Let's think about what the cumulative is telling us here. Because we are looking at a continuous distribution with values that stretch from negative infinity to positive infinity, norm. dist will return a cumulative probability from the negative infinity up to the X value that you've specified."
Text: Norm dot dist with Cumulative set to True returns the P left parenthesis x greater than or equal to 150000 right parenthesis.
Narrator: "We have about 6. 5% of homes sold for less than $150,000, assuming that we have a normally distributed population. In the next question, when we're asked to answer what percentage of homes sold for greater than $280,000, the cumulative probability that we find when we put an X of $280,000 will be the probability of selling up to $280,000."
Text: Norm dot dist would return P left parenthesis x less than 280,000 right parenthesis.
Narrator: "To find the answer for this question, we have to subtract that probability from 100%."
Text: one minus norm dot dist will return P left parenthesis x greater than 280000 right parenthesis.
Narrator: "Let's solve it in the cell. Equals 1 minus norm. dist. And when I put the left parenthesis, it will tell me what to put, so I don't need the dialogue box."
A text box below the cell lists the categories of values to be inputted.
Narrator: "My X is $280,000. Comma, my mean, my standard deviation, and then True for cumulative."
The full formula in cell E12: equals one minus norm dot dist left parenthesis 280000 comma B5 comma G5 comma true right parenthesis.
Narrator: "About 10. 5% of homes sold for greater than $280,000. Finally, we want to find what percentage of homes sold for between $150,000 and $280,000."
P left parenthesis 150000 less than x less than 280000 right parenthesis.
Narrator: "This is a two-part calculation. We want to find what percentage of homes sold for less than $280,000. And then we want to find what percentage of homes sold for less than $150,000."
First: P left parenthesis x less than 280000 right parenthesis equals question mark. Second: P left parenthesis x less than 150000 right parenthesis equals question mark.
Narrator: "When we subtract that second piece from the first piece, we'll find the overlapping area that gives us the percentage of homes that sold between $150,000 and $280,000."
P left parenthesis x less than 280000 right parenthesis minus P left parenthesis x less than 150000 right parenthesis equals P left parenthesis 150000 less than x less than 280000 right parenthesis.
Narrator: "Again, we can do the entire thing in a single cell—equals norm. dist. And we put in 280,000 for the X, the mean, the standard deviation, True, close parentheses, minus norm. dist, the X of 150,000, the mean, the standard deviation. I can double-click on True and it will insert if for me. Now, when I hit Enter, it's going to give me the area under the curve that falls between $150,000 and $280,000, which is about 82. 9%."
The full formula in cell E13: equals norm dot dist left parenthesis 280000 comma B5 comma G5 comma true right parenthesis minus NORM dot DIST left parenthesis 150000 comma B5 comma G5 comma True right parenthesis.
Narrator: "In this set of three questions, we have found the probability of homes selling for less than $150,000, $150,000 to $280,000, and more than $280,000. That is the entire population of homes. When we add all three answers, we should get 100%. I'll select them with the mouse key, and at the bottom, you can see the sum adds to 1."
With cells E11 to E13 highlighted, the bottom of the screen lists the average, count, and sum, which is one.
Narrator: "This video has demonstrated how to use norm. dist to find the cumulative probability of area under the curve in a normal distribution."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.