for Statistics Managers Using Microsoft Excel
4th Edition
Chapter 2 Presenting Data in Tables and Charts
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-1
Chapter Goals
After completing this chapter, you should be able to:
Create an ordered array and a stem-and-leaf display Construct and interpret a frequency distribution, polygon, and ogive Construct a histogram Create and interpret bar charts, pie charts, and scatter diagrams Present and interpret category data in bar charts and pie charts Describe appropriate and inappropriate ways to display data graphically
Chap 2-2
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Organizing and Presenting Data Graphically
Data in raw form are usually not easy to use for decision making
Some type of organization is needed
Table Graph
Techniques reviewed here:
Ordered Array Stem-and-Leaf Display Frequency Distributions and Histograms Bar charts and pie charts Contingency tables
Chap 2-3
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Tables and Charts for Numerical Data
Numerical Data
Ordered Array
Frequency Distributions and Cumulative Distributions Histogram Polygon Ogive
Stem-and-Leaf Display
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-4
The Ordered Array
A sorted list of data:
Shows range (min to max) Provides some signals about variability within the range May help identify outliers (unusual observations) If the data set is large, the ordered array is less useful
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-5
The Ordered Array
(continued)
Data in raw form (as collected): 24, 26, 24, 21, 27, 27, 30, 41, 32, 38
Data in ordered array from smallest to largest: 21, 24, 24, 26, 27, 27, 30, 32, 38, 41
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-6
Stem-and-Leaf Diagram
A simple way to see distribution details in a data set METHOD: Separate the sorted data series into leading digits (the stem) and the trailing digits (the leaves)
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-7
Example
Data in ordered array:
21, 24, 24, 26, 27, 27, 30, 32, 38, 41
Here, use the 10's digit for the stem unit:
Stem Leaf
21 is shown as 38 is shown as
2 3
1 8
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-8
Example
(continued)
Data in ordered array:
21, 24, 24, 26, 27, 27, 30, 32, 38, 41
Completed stem-and-leaf diagram:
Stem Leaves
2 3 4
1 4 4 6 7 7 0 2 8 1
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-9
Using other stem units
Using the 100's digit as the stem:
Round off the 10's digit to form the leaves
Stem
Leaf 1 8 2
613 would become 776 would become ... 1224 becomes
6 7 12
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-10
Using other stem units
(continued)
Using the 100's digit as the stem:
The completed stem-and-leaf display:
Data:
613, 632, 658, 717, 722, 750, 776, 827, 841, 859, 863, 891, 894, 906, 928, 933, 955, 982, 1034, 1047,1056, 1140, 1169, 1224 Stem 6 7 8 9 10 11 12 Leaves 136 2258 346699 13368 356 47 2
Chap 2-11
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Tabulating Numerical Data: Frequency Distributions
What is a Frequency Distribution?
A frequency distribution is a list or a table ... containing class groupings (categories or ranges within which the data fall) ... and the corresponding frequencies with which data fall within each grouping or category
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-12
Why Use Frequency Distributions?
A frequency distribution is a way to summarize data The distribution condenses the raw data into a more useful form... and allows for a quick visual interpretation of the data
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-13
Class Intervals and Class Boundaries
Each class grouping has the same width Determine the width of each interval by
range Width of int erval number of desired class groupings
Use at least 5 but no more than 15 groupings Class boundaries never overlap Round up the interval width to get desirable endpoints
Chap 2-14
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Frequency Distribution Example
Example: A manufacturer of insulation randomly selects 20 winter days and records the daily high temperature 24, 35, 17, 21, 24, 37, 26, 46, 58, 30, 32, 13, 12, 38, 41, 43, 44, 27, 53, 27
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-15
Frequency Distribution Example
(continued)
Sort raw data in ascending order:
12, 13, 17, 21, 24, 24, 26, 27, 27, 30, 32, 35, 37, 38, 41, 43, 44, 46, 53, 58
Find range: 58 - 12 = 46 Select number of classes: 5 (usually between 5 and 15) Compute class interval (width): 10 (46/5 then round up) Determine class boundaries (limits): 10, 20, 30, 40, 50, 60 Compute class midpoints: 15, 25, 35, 45, 55 Count observations & assign to classes
Chap 2-16
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Frequency Distribution Example
(continued)
Data in ordered array:
12, 13, 17, 21, 24, 24, 26, 27, 27, 30, 32, 35, 37, 38, 41, 43, 44, 46, 53, 58 Relative Frequency
Class
Frequency
Percentage
10 but less than 20 20 but less than 30 30 but less than 40 40 but less than 50 50 but less than 60
3 6 5 4 2
.15 .30 .25 .20 .10
15 30 25 20 10
Chap 2-17
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Graphing Numerical Data: The Histogram
A graph of the data in a frequency distribution is called a histogram The class boundaries (or class midpoints) are shown on the horizontal axis the vertical axis is either frequency, relative frequency, or percentage Bars of the appropriate heights are used to represent the number of observations within each class
Chap 2-18
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Histogram Example
Class 10 but less than 20 20 but less than 30 30 but less than 40 40 but less than 50 50 but less than 60 Class Midpoint Frequency 15 25 35 45 55 3 6 5 4 2
Histogram : Daily High Tem perature 7 6 Frequency 5 4 3 2 1 0 5 15 0 0 3 2 6 5 4
(No gaps between bars)
Class Midpoints
25
35
45
55
More
Chap 2-19
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Histograms in Excel
1 Select Tools/Data Analysis
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-20
Histograms in Excel
(continued)
2 Choose Histogram
(
3 Input data range and bin range (bin range is a cell
range containing the upper class boundaries for each class grouping)
Select Chart Output and click "OK"
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc. Chap 2-21
Questions for Grouping Data into Classes
1. How wide should each interval be?
(How many classes should be used?)
2. How should the endpoints of the intervals be determined?
Often answered by trial and error, subject to user judgment The goal is to create a distribution that is neither too "jagged" nor too "blocky" Goal is to appropriately show the pattern of variation in the data
Chap 2-22
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
How Many Class Intervals?
Many (Narrow class intervals)
3.5 3 2.5 Frequency 2 1.5 1 0.5 0 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 More
may yield a very jagged distribution with gaps from empty classes Can give a poor indication of how frequency varies across classes
Temperature
Few (Wide class intervals)
12 10 Frequency 8 6 4 2 0 0 30 60 More Temperature
may compress variation too much and yield a blocky distribution can obscure important patterns of variation.
(X axis labels are upper class endpoints)
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap Numerical 2-23
Graphing Data: The Frequency Polygon
Class 10 but less than 20 20 but less than 30 30 but less than 40 40 but less than 50 50 but less than 60 Class Midpoint Frequency 15 25 35 45 55 3 6 5 4 2
Frequency Polygon: Daily High Temperature 7 6 5 Frequency 4 3 2 1 0 5 15 25 35 45 55 More
Chap 2-24
(In a percentage polygon the vertical axis would be defined to show the percentage of observations per class)
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Class Midpoints
Tabulating Numerical Data: Cumulative Frequency
Data in ordered array:
12, 13, 17, 21, 24, 24, 26, 27, 27, 30, 32, 35, 37, 38, 41, 43, 44, 46, 53, 58 Cumulative Cumulative Frequency Percentage 3 9 14 18 20 15 45 70 90 100
Class 10 but less than 20 20 but less than 30 30 but less than 40 40 but less than 50 50 but less than 60 Total
Frequency Percentage 3 6 5 4 2 20 15 30 25 20 10 100
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-25
Graphing Cumulative Frequencies:
The Ogive (Cumulative % Polygon)
Lower Cumulative class boundary Percentage 10 20 30 40 50 60 0 15 45 70 90 100 Class Less than 10 10 but less than 20 20 but less than 30 30 but less than 40 40 but less than 50 50 but less than 60
Ogive: Daily High Temperature
Cumulative Percentage
100 80 60 40 20 0 10 20 30 40 50 60
Class Boundaries (Not Midpoints)
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc. Chap 2-26
Scatter Diagrams
Scatter Diagrams are used for bivariate numerical data
Bivariate data consists of paired observations taken from two numerical variables
The Scatter Diagram: one variable is measured on the vertical axis and the other variable is measured on the horizontal axis
Chap 2-27
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Scatter Diagram Example
Volume per day 23 26 29 33 38 42 50 55 60 Cost per day 125 140 146 160 167 170 188 195 200
Cost per Day vs. Production Volume
250 200 Cost per Day 150 100 50 0 0 10 20 30 40 50 60 70 Volume per Day
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-28
Scatter Diagrams in Excel
1
Select the chart wizard
2
Select XY(Scatter) option, then click "Next"
3
When prompted, enter the data range, desired legend, and desired destination to complete the scatter diagram
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc. Chap 2-29
Tables and Charts for Categorical Data
Categorical Data
Tabulating Data Summary Table Bar Charts
Graphing Data
Pie Charts
Pareto Diagram
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-30
The Summary Table
Summarize data by category Example: Current Investment Portfolio
Investment Amount Percentage Type (in thousands $) (%) Stocks Bonds CD Savings Total 46.5 32.0 15.5 16.0 110.0 42.27 29.09 14.09 14.55 100.0
Chap 2-31
(Variables are Categorical)
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Bar and Pie Charts
Bar charts and Pie charts are often used for qualitative (category) data Height of bar or size of pie slice shows the frequency or percentage for each category
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-32
Bar Chart Example
Current Investment Portfolio
Investment Type
(in thousands $)
Amount
Percentage
(%)
Stocks Bonds CD Savings Total
46.5 32.0 15.5 16.0 110.0
42.27 29.09 14.09 14.55 100.0
Savings CD Bonds Stocks 0
Investor's Portfolio
10
20
30
40
50
Chap 2-33
Amount in $1000's
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Pie Chart Example
Current Investment Portfolio
Investment Type
(in thousands $)
Amount
Percentage
(%)
Stocks Bonds CD Savings Total
46.5 32.0 15.5 16.0 110.0
42.27 29.09 14.09 14.55 100.0 CD 14%
Savings 15%
Stocks 42%
Bonds 29%
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Percentages are rounded to the nearest percent
Chap 2-34
Pareto Diagram
Used to portray categorical data A bar chart, where categories are shown in descending order of frequency A cumulative polygon is often shown in the same graph Used to separate the "vital few" from the "trivial many"
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-35
Pareto Diagram Example
Current Investment Portfolio
45% 100% 90%
% invested in each category (bar graph)
40%
35%
80% 70%
cumulative % invested (line graph)
30% 60% 25% 50% 20% 40% 15% 30% 10% 20%
5%
10% 0% Stocks Bonds Savings CD
0%
Statistics for Managers Using Microsoft Excel, 4e 2004 Prentice-Hall, Inc.
Chap 2-36
Tabulating and Graphing Multivariate Categorical Data
Contingency Table for Investment Choices ($1000's)
Investor A Investor B Investor C Total
Investment Category
Stocks Bonds CD Savings To...