When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Descriptive Statistics and the Empirical Rule. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "This video demonstrates how to use Excel's descriptive statistics output to determine whether a set of data is normally distributed. And, for normally distributed data, how to use the output to determine the upper and lower limits which will capture 68%, 95%, and 99. 7% of the data. In this data set, we have the blood-glucose readings for February for a person recently diagnosed with Type 2 diabetes. We can quickly determine if the data are normally distributed—that is, if they are symmetrically distributed around the mean—by running the descriptive statistics feature in Excel. Go to the Data tab, click on 'Data Analysis,' and choose 'Descriptive Statistics.' Click 'Okay.'"
The Data Analysis button is found in the Analysis section of the Data tab. When clicked, the Data Analysis dialog box appears with a list of Analysis Tools. Descriptive Statistics is selected from the list. When OK is clicked, the Descriptive Statistics dialog box appears.
Narrator: "Put your cursor in the input range and then choose column 'A.' I'll select 'Labels in first row,' since 'Reading' was included in the values I captured. And I'd like to have my output be on this spreadsheet rather than on a new worksheet. I'll select 'Summary Statistics' and click 'Okay.'"
Within the Descriptive Statistics dialog box, Input Range is set to $A:$A, the Labels in first row checkbox is checked, the Output Range radio button is selected to enter $C$2 in its text field, and the Summary statistics checkbox is checked.
When OK is clicked, data is applied to columns C and D. Column C is widened by dragging the headers right border.
Narrator: "And I'll widen the column so that we can see everything. Note that the mean and the median are very close to one another. The mean is 112. 86. The median is 112. The closer these two values are, the more symmetric our data tends to be. Data that are perfectly normally distributed will have a mean equal to the median equal to the mode. But we can still apply the empirical rule if our data have some small amount of variation. The Empirical Rule states that, given normally-distributed data, 68% of the results will be within one standard deviation of the mean. Approximately 95% will be within two standard deviations of the mean. And 99. 7%, or virtually all of the data, will be within three standard deviations of the mean. We can use the results from our descriptive statistics table to calculate these 68%, 95%, and 99. 7% limits by using the mean and the standard deviation. I've set up a table in which I'll put the output of those calculations."
Lower limit and upper limit are entered into cells F3 and G3 respectively. 1s, 2s, and 3s are entered into cells E4 to E6.
Narrator: "For one standard deviation, it will be the mean minus the standard deviation."
Equals D4 minus D8 is entered into cell F4.
Narrator: "And for the upper limit, it will be the mean plus one standard deviation."
Equals D4 plus D8 is entered into cell G4.
Narrator: "I've finished filling in the table for the two-standard deviation lower limit. It will be the mean minus 2 times the standard deviation."
Equals D4 minus left parenthesis 2 times D8 right parenthesis is entered into cell F5.
Narrator: "And for the upper, it will be the mean plus 2 times standard deviation."
Equals D4 plus left parenthesis 2 times D8 right parenthesis is entered into cell G5.
Narrator: "For the lower limit, it will be mean minus 3 times standard deviation. And for the upper limit, it will be the mean plus 3 times standard deviation."
Equals D4 minus left parenthesis 3 times D8 right parenthesis is entered into cell F6. The equation in cell G6, containing 130.04786, is not shown.
Narrator: "Recall that I mentioned, in the Empirical Rule, 68% of our data are within one standard deviation, 95% within two. Let's sort our data smallest to largest."
With column A highlighted, the Sort button, found in the Editing section of the Home tab, is expanded to select Sort Smallest to Largest.
Narrator: "We can see the smallest value is 96, which we can also see in our descriptive statistics table, and the largest value is 127. If you look at the lower and upper limits we've calculated, 96 is just outside of our two-standard deviation limit. It fits within three standard deviations. Our maximum value of 127 fits within the upper limit. So, as you can see here, this data is not that far dispersed around the mean. Virtually all values fit within two standard deviations. In this video, you have seen how to use Excel's descriptive statistics output to determine whether data are normally distributed by comparing the mean with the median. And, for normally distributed data, how the Empirical Rule describes the percent of data within one, two, and three standard deviations of the mean."
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.