The purpose of this exercise is to learn how to create histograms and find descriptive statistics using Excel.
The following table has been filled in with the height of each student (measured in centimeters) from a previous Math class. You will use the complete data set to construct a histogram, find descriptive statistics for the data set, and answer the accompanying questions.
Class Height Data
(Measured in centimeters)
| 186 | 175 | 160 | 169 | 170 | 174 | ||||
| 160 | 187 | 164 | 161 | 186 | 186 | ||||
| 163 | 187 | 168 | 179 | 164 | 178 | ||||
| 174 | 175 | 186 | 184 | 166 | 175 | ||||
| 178 | 180 | 180 | 182 | 175 | 165 | ||||
| 170 | 186 | 179 | 169 | 178 | 184 | ||||
| 173 | 167 | 180 | 172 | 160 | |||||
| 166 | 162 | 176 | 167 | 160 |
Follow the “Excel Directions” section of this document to find the descriptive statistics and to construct a histogram of the data set within Excel. You will need to submit your Excel spreadsheet, including the data entered, the statistics, and the histogram (formatted as specified in the “Formatting the Histogram” section of this document). You will also need to submit your answers to the questions below.
- What is the smallest data value?
- What is the largest data value?
- What is the class width?
- The bins used in Excel represent the upper class limits for each class. Assuming class widths are equal, what are the lower and upper class limits for the third class?
Lower class limit: Upper class limit:
- How many recorded data values are in the third class?
- What percentage of the recorded data values are in the third class?
- Which class contains the fewest data values?
h. Mean = __________ Standard deviation = Range =
i. 5-Number Summary: Min. =
Q1 =
Med. =
Q3 =
Max. =
j. Draw a boxplot below by hand.
EXCEL Directions:
To begin the project, open Excel, type the word “Height” in the first cell in the first column (column A), and then enter all the height data below. Sort the data by clicking on any cell in column A, and then clicking on the sort icon (it’s the AZ with a down arrow). This will make it easier to spot outliers in the data. Type the word “bins” in the first cell in column B. The bin numbers represent the upper class limits for each class in the frequency distribution. The bin numbers entered in column B will depend upon the height values in the table. We will be using multiples of five for our bin numbers. The first bin number will be the multiple of five closest to and greater than or equal to the smallest data value. For example, if the smallest data value is 148, the first bin number will be 150. The last bin number will be the multiple of five closest to and greater than or equal to the largest data value. For example, if the largest data value is 193, the last bin number will be 195. Once you have determined the first and last bin numbers, enter the first bin number in column B directly under the word “bins” and continue entering all consecutive multiples of five with the last entry being the largest bin number. For example, if the first bin number is 150 and the last bin number is 195, you should enter the following numbers in column B under the word “bins”: 150, 155, 160, 165, 170, 175, 180, 185, 190, 195.
Installing the Analysis ToolPak
Before you use the Histogram tool, you need to make sure the Analysis ToolPak Add-in is installed. To verify that the Analysis ToolPak is installed, follow these steps:
- In Microsoft Office Excel 2003, click Add-Ins on the Tools menu.
In Microsoft Office Excel 2007, follow these steps:
- Click the Microsoft Office Button, and then click Excel Options.
- Click the Add-Ins category.
- In the Manage list, select Excel Add-ins, and then click Go.
In Microsoft Office Excel 2016, follow these steps:
- Click File and then click Options. An Excel Options window will open.
- Click Add-ins, then click Analysis ToolPak, and then click Go. An Add-ins window will open.
- In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available is selected. Click OK.
NOTE: In order for the Analysis ToolPak to be shown in the Add-Ins dialog box, it must be installed on your computer. If you do not see Analysis ToolPak in the Add-Ins dialog box, run Microsoft Excel Setup and add this component to the list of installed items.
Creating a Histogram
- In Excel 2003 click Data Analysis on the Tools menu.
In Excel 2007 and 2016, click on the Data tab, and then click on the Data Analysis link in the Analysis group. - In the Data Analysis dialog box, click Histogram, and then click OK.
A new window titled Histogram should appear. This window has many options. Below is a brief explanation of each:
- Input Range is where the data being used to create the histogram are located. Put your cursor back into the spread sheet and highlight the variable name and all the data in the first column (column A).
- Bin Range is used to specify your own class breaks. Put your cursor back into the spread sheet and highlight the variable name and all the data in the second column (column B).
- Click Labels. If a variable name was highlighted in the Input Range and the Bin Range, then this must be checked.
- You must select one of the following Output options:
Click Output Range if you want the histogram to be placed on the current sheet. Click on the cell in the spread sheet where you want the histogram to appear.
Click New Worksheet Ply if you want the histogram to be placed on a new sheet.
e. Click Chart Output. This step is necessary to obtain the histogram. If this is not selected, you will only receive a frequency distribution chart.
- Click OK. The histogram and frequency distribution chart should be placed onto your spread sheet.
Formatting the Histogram
Excel 2003 Directions
- Click anywhere inside the histogram to activate it.
- To close the gaps between the bars, click on one of the bars. This will bring up the Format Data Series window. Click on Options and change the gap width to 0.
- While in the Format Data Series window, click on Data Labels and select Value. This will show the frequency count for each bar in the histogram.
- Scroll across the x-axis until you see Category Axis appear in a yellow box. Double click on Category Axis. This will bring up the Format Axis window. There are five options: Patterns, Scale, Font, Number, and Alignment. You do not need to modify any of the settings except in Alignment.
- Click on Alignment and change the degree setting to -45. (You may do this by typing in -45 in the box provided, or you can click on the text line and rotate it -45 degrees.) This will properly align the bin numbers on the x-axis.
- To change titles and headings, simply double click on the title/heading you wish to change and type in the new title/heading. Change “Bins” to “Height” and change “Histogram” to “MAT 121 Histogram by (Your Name)”.
Excel 2007 Directions
- Click inside the histogram to activate it. There are different steps that can be taken to format your histogram, but the easiest approach is to right click your mouse over the area of the histogram you wish to change.
- To close the gap width, right click on one of the histogram bars, click on the Format Data Series link (last link in the menu that appears), set the gap width to 0%, and click Close.
- To outline the bars, right click on one of the histogram bars, click on the Format Data Series link (last link in the menu that appears), click on Border Color, select Solid line, use the drop down menu to change the color to black or another contrasting color, and click Close.
- To insert the frequency counts above each bar, right click on one of the histogram bars again, click on the Format Data Labels link (next to the last link in the menu that appears), check the Value box, and Outside End as the Label Position, and click Close.
- To align the values on the horizontal axis, right click on one of the horizontal axis values, click on Format Axis, click on the Alignment link on the left, change the Custom angle to 45°, click Close.
- To insert your name in the title and change the word “bins” to “height”, click on the text in the histogram and make the necessary changes.
Excel 2016 Directions
- Click inside the histogram to activate it. There are different steps that can be taken to format your histogram, but the easiest approach is to right click your mouse over the area of the histogram you wish to change.
- To close the gap width, right click on one of the histogram bars, click on the Format Data Series link (last link in the menu that appears), set the gap width to 0%, and click Close.
- To outline the bars, right click on one of the histogram bars, click on the Format Data Series link (last link in the menu that appears), click on Fill & Line, then click Border and select Solid line, use the drop down menu to change the color to black or another contrasting color, and click Close.
- To insert the frequency counts above each bar, left click anywhere in the histogram, click on the + sign to the right of the histogram (called “Chart Elements”), and check the Data Labels box.
- To align the values on the horizontal axis, right click on one of the horizontal axis values and click on Format Axis. Then click on the symbol that looks like a box with 4 arrows in it (called “Size & Properties”). Under the Alignment tab, change the Custom angle to 45° and click Close.
- To insert your name in the title and change the word “bins” to “height”, click on the text in the histogram and make the necessary changes.
Finding Descriptive Statistics
Excel 2003 Directions
- Click on TOOLS, then click on Data Analysis.
- In the dialog box, click on Descriptive Statistics, then click OK
- You should now see a “Descriptive Statistics” dialog box. Highlight the data cells to paste the data in the Input Range. Make sure you include the label name “HEIGHT” (in the first cell) as part of your data input. To account for this, you must check the Labels in the First Row box.
- Select Output Range if you want the summary statistics on your current worksheet and check the Summary Statistics box.
- Click OK and you will get a display of descriptive measures including the mean, median, standard deviation, range, minimum, and maximum.
Excel 2007 and 2016 Directions
- Click on the Data tab, click on Data Analysis, select Descriptive Statistics, and follow the steps outlined above for Excel 2003.
Finding Quartiles
To paste the first and third quartiles (Q1 and Q3) into your worksheet (using both Excel 2003 and 2007), click on the fx button on the toolbar, select Statistical as the category (use the drop down menu as needed), scroll down to select Quartile as the function, click on OK. In the array box, click on Column A; in the Quart box, enter in 1 (this will return the 1st quartile of the data set). Click on OK. Repeat the process, only this time enter in 3 in the Quart box (this will return the 3rd quartile of the data set).
Excel 2016 Directions
- Click on an empty cell, click Formulas, then More Functions, and then Statistical.
- Scroll down until you see QUARTILE.EXC and click it. A window called Function Arguments will appear. (Note that there are two types of Quartile computation methods. The QUARTILE.EXC command matches the method taught by our textbook.)
- For Array, highlight the data cells from column A. Next to Quart, type 1 to find the 1st Quartile.
- Click OK . To find the 3rd Quartile, you will repeat these steps except you will type 3 instead of 1 next to “Quart” in the Function Arguments window.
