Download Excel Descriptive Statistics: Individual and Summary and more Schemes and Mind Maps Descriptive statistics in PDF only on Docsity!
Using Excel, Chapter 2:
Descriptive Statistics
- Individual Descriptive Statistics using Excel Functions 2
- A Summary of Descriptive Statistics Using the Analysis ToolPak (Windows Users) 3
- A Summary of Descriptive Statistics Using StatPlus (Mac Users) 4
- Getting and Running the Analysis ToolPak and StatPlus LE 5
Individual Descriptive Statistics using Excel Functions
- Averages:
- The mean is calculated using the AVERAGE(cell range) function.
- The median is calculated using the MEDIAN(cell range) function.
- The mode is calculated using the MODE(cell range) function.
- Range, Variance, and Standard Deviation:
- The range is calculated using the MIN and MAX functions.
- The sample variance is calculated using the VAR.S or VAR (older versions) function.
- The sample standard deviation is calculated using the STDEV.S or STDEV (older versions) function.
- The population variance and standard deviation are calculated using the VAR.P and STDEV.P functions respectively.
- Percentiles & Quartiles
- The kth^ percentile is found by Pk = PERCENTILE(Data Range, k in decimal form)
- The first quartile (Q 1 ) is found by PERCENTILE(Data Range, 0.25)
- The the third quartile (Q 3 ) is found by PERCENTILE(Data Range, 0.75)
- Box-Plots and 5-Number Summaries: Excel is terrible with box-plots. There are various add-in’s you can find. I find the easiest method is to grab a free-use template from the internet. There are many. Just search boxplot Excel Version. You enter the data into the correct location and a graph of the box-plot is made via a sequence of non-standard graphing tricks.
There is no single command to get Excel to make a 5-number summary but you can piece it together pretty easily. 5-number summary: Excel Commands min MIN(Data Range) Q 1 PERCENTILE(Data Range, 0.25) Q 2 MEDIAN(Data Range) Q 3 PERCENTILE(Data Range, 0.75) max MAX(Data Range)
- A Summary of Descriptive Statistics Using StatPlus
Mac Users Only
The Analysis ToolPak which comes free with every Windows version of Excel is not available for Mac Excel. There is an application called StatPlus:Mac LE (http://www.analystsoft.com/en/products/statplusmacle/). This is a free version of the full StatPlus application. It is actually better than the Analysis ToolPak but you have to run it alongside Excel as opposed to directly within Excel. So you have to get accustomed to running them simultaneously. Once you have downloaded the software, you can get a nice summary of descriptive statistics by following the sequence below.
- Run StatPlus from the Applications folder. It automatically opens Excel.
- Click Statistics.
- Choose Basic Statistics and Tables.
- Choose Descriptive Statistics.
- Select the range of cells in which the data lies.
- Check Labels in first row only if you highlighted the column headings.
- Click ’OK’.
- The results are printed on a separate StatPlus page.
- You can cut and paste the results back into your Excel spreadsheet if you want.
- Getting and Running the Analysis ToolPak and StatPlus LE
- Installing Analysis ToolPak (Windows Only)
- Open a blank Excel spreadsheet.
- Click on the windows icon (pre 2010) or the file tab (2010+).
- Choose Excel Options (pre 2010) or just options (2010+).
- Choose add-ins.
- In manage (bottom of window), choose Excel Add-ins and click Go.
- Check the box that says Analysis ToolPak and click OK.
- After you load the Analysis ToolPak, the Data Analysis command is available under the Data tab. It should be the far right option.
- Getting StatPlus LE (Mac Only) As of this writing, if you are running Excel 2008 or higher on a Mac, the Analysis ToolPak is not available. There is an application called StatPlus:mac LE which is a free version of the full StatPlus application. It can handle most of the tasks performed by the Analysis ToolPak and the full version is probably superior - but that costs money. Once you download the software from http://www.analystsoft.com/en/products/statplusmacle/, 1. Run StatPlus from the Applications Folder. 2. If you don’t already have Excel open, it will open it for you. 3. You will run the commands from the StatPlus menu (top menu bar). 4. Choose the appropriate cells from the Excel worksheet containing the data. 5. The results are printed to a separate Excel worksheet. 6. You can then cut and paste these results in the Excel Worksheet that contains the data.
