How to use the Excel Spreadsheet provided to generate descriptive statistics
On the Data and Descriptive Statistics tab, move to cell A1. You can type the numbers in the blue cells, and the spreadsheet will calculate the descriptive statistics. To use the Copy and Paste command, download the Excel spreadsheet DescriptiveStatistics.xlsx (click the link to download the Excel spreadsheet). Another way to download the Excel Workbook is to click the link below.
You will only be able to enter values in the blue cells.
Suppose your data is as follows.
Starting in cell A1 enter the data one point at a time.
Once the data is entered into the Excel spreadsheet, the values are calculated for the following information.
- The sample size, n, is in cell E1.
- The mean is in cell E2.
- The median is in cell E3
- The Mode is in cell E4
- The range is in cell E5.
- The first Quartile is in cell E6.
- The third Quartile is in cell E7.
- The interquartile range is in cell E8.
- The low limit for outliers using Quartiles is in cell E9.
- The upper limit for outlier using Quartile is in cell E10.
- The standard deviation is in cell E11.
- The variance is in cell E12.
- The minimum is in cell E13.
- The maximum is in cell E14.
- The Coefficient of Variation is in cell E20 for sample data in F20 if the data represents all elements in the population.
- The Empirical Rule Intervals are in cells M17 thru P19.
- The Tchebysheff's Theorem's Intervals are in cells M8 thru P10.
The final Excel spreadsheet will display the following information.
|First Quartile Q1 (There are at least seven ways to compute this value)||500.3|
|Third Quartile Q3 (There are at least seven ways to compute this value)||642.8|
|Interquartile Range (Q3 - Q1)||142.5|
|Lower Limit for Outliers (Values below this value are considered outliers)||286.55|
|Upper Limit for Outliers (Values above this value are considered outliers)||856.55|
|Standard Deviation (Sample/Population)||103/98.9592|
|Coefficient of Variation (Sample/Population)||17.59781/16.90743|
|Empirical Rule (68% of data between) (Sample/Population)||[482.3, 688.3]/[486.3408, 684.2592]|
|Empirical Rule (95% of data between) (Sample/Population)||[379.3, 791.3]/[387.3816, 783.2184]|
|Empirical Rule (99.7% of data between) (Sample/Population)||[276.3, 894.3]/[288.4224, 882.1776]|
|Tchebysheff's Theorem (at least 75%) (Sample/Population)||[379.3, 791.3]/[387.3816, 783.2184]|
|Tchebysheff's Theorem (at least 89%) (Sample/Population)||[276.3, 894.3]/[288.4224, 882.1776]|
|Tchebysheff's Theorem (at least 95%) (Sample/Population)||[173.3, 997.3]/[189.4632, 981.1368]|