Graphical (and Tabular) Descriptions of Data

n  Tabulating and Graphing Univariate and Bivariate qualitative data

n  Organizing numerical data

n  Tabulating and graphing Univariate numerical data

n  Graphing Bivariate numerical data

n  Using Excel

Purpose: To illustrate how to display the distribution of values.

1. Tabulating and Graphing Categorical Data

1.1 Univariate Data

·  Example: letter grades on first exam

·  Count the number and percent of times each value of the data occurs

·  Plot the counts and/or percents in a bar chart or pie chart

·  Link to Excel 2013 Directions for a bar chart

1.2 Bivariate Categorical Data

·  Example: Letter grades on first and second exam

·  Count the number of occurrences of each combination of values (Contingency table)

·  Link to Excel 2013 Directions for side-by-side bar charts

2. Organizing Numerical Data

n  ordered array sorts the numbers from smallest to largest: 21, 24, 24, 26, 27, 27, 30, 32, 38, 41

3. Tabulating and Graphing Numerical Data

3.1 Tabulating Numerical Data: Frequency Distributions

Divide data into intervals and determine the

·  count and percent of data in each interval

·  count and percent of the data less than or equal to the upper boundary of each interval

3.2 Graphing Numerical Data:

·  Histogram – draws a bar above each interval indicating the count

·  Relative Frequency Histogram – draws a bar above interval indicating the percent

·  Frequency and Relative Frequency Polygon – draws a line connecting tops of each bar

·  Cumulative Frequency Polygon (or Ogive) – draws a line indicating the cumulative percentages

·  Link to Excel Directions for a histogram and

·  Excel Directions for side-by-side histograms

4 Graphing Bivariate Numerical Data (Scatter Plot)

·  Plot points on a XY scatter plot graph

·  Link to Excel Directions for a scatter plot

5. Overview

Graphically Describing One Variable
Type of Data / Graph
One Qualitative / Bar or Pie Chart
One Quantitative / Histogram or Relative Frequency Histogram,
Frequency or Relative Frequency Polygon,
Ogive
Graphically Describing The Relationship Between Two Variables
Type of Data / Graph
Both Qualitative / Side by Side Bar or Pie Chart
Both Quantitative / Scatter plot
One Quantitative with one Qualitative / Side by Side Histograms, Polygons or Ogives

6. Using Excel

6.1 Histogram and Cumulative Relative Frequency Polygon

1.  Highlight the column of interest. Be sure that the first row is a label.

2.  Click on the Insert tab of Excel and choose Pivot Table

3.  The Table/Range should show the column you selected. Click the Ok button

4.  In the PivotTable Fields on the right drag the variable name to the Rows section and the S Values section at the bottom.

5.  Right click on any number in the rows and choose Group. Note if you get an error at this point, then copy and paste the original data into a new Workbook and try again.

6.  In the Grouping Box choose the starting value, the end value and the width of each interval.

7.  The resulting table will have ranges and counts within each range:

8.  To change counts to percentages, first click on the “Count of …” column in the pivot and choose Value Field Settings

9.  Click on the tab “Show Values As” and then click on down arrow in the next box and choose “% of Grand Total”

10.  To clean up the table, click the down arrow next to Row Labels and uncheck the (Blank) Row.

11.  To obtain a bar chart, highlight every cell but the Grand Total Row, click the Insert tab and choose Column chart

12.  To make it a histogram, right click on any bar choose Format Data series. Change the Gap Width to 0 (zero). You can also change the Border color around the bars to black.

13. In Format Data Series window you will also see an icon that looks like a can of paint being poured. Click on this to create borders around each bar.


14. Clicking on + button at the top right of the graph will allow you to insert axis labels.

6.2 Scatterplot

·  Be sure X is adjacent and to the left of Y

·  Choose rows of interest

·  Choose Insert tab and choose Scatter from list of Chart types

·  From the 5 choices of Scatter plots, choose the first one

6.3 Bar Charts

·  Use the same approach as in Histograms but do not use the Group menu item and do not change Gap Widths to zero

6.4 Drilling Down, Histogram for Each Level of a Qualitative Variable

•  Highlight the qualitative and quantitative columns.

•  As before drag the quantitative variable to the “Row” section of the pivot table and the “S Values” section

•  Drag the qualitative variable to the Filter section

•  Repeat the steps for the Histogram

•  You will now have a variable in row A that is the filter and will allow you to create percentages for only the levels of the qualitative that you choose. For example, I am pretending that Nbeds is a qualitative variable and wish to see the distribution of finished area for 1 bed room homes, 2 bedroom homes, 3 bedroom homes, etc. By clicking on the filter down arrow in cell A2, I have chosen in the picture below to show only three bedroom houses. (You will need to change the title, copy and paste this (see above to a Word document)

• 

6.5 Side by Side Bar Charts

•  Same steps as in the Bar chart except you will drag the second qualitative variable to the “Column” box in the bottom right

7. In class Exercise.

Click below for an Excel spread sheet and follow the directions.

(Any cell that has a red triangle indicates there is a comment there.

.)

Click here for Graphing Exercise