1

1
Business 90 Business Statistics Professor
David Mease Sec 01, T Th 900-1015AM BBC
202 Lecture 7 More of Chapter Presenting
Data in Tables and Charts (PDITAC)
Agenda 1) Reminder about Homework 2 due
Thursday 2) Lecture over more of Chapter
PDITAC 3) Discuss grades and missing pictures 4)
Helpful hints for watching lecture videos 5)
Start lecturing over second part of Chapter PDITAC
2
Homework 2- Due Thursday 2/12

• 1) Read the chapter entitled Presenting Data in
  Tables and Charts
• 2) The Excel file at http//www.cob.sjsu.edu/mease
  _d/old-quiz-scores.xls has Quiz 1 scores for a
  Bus 90 class I thought last semester. Right click
  this link and select "Save Target As..." to
  download this file onto your computer. Then open
  it using Excel.
• a) Make the frequency distribution by hand. Begin
  at 0 and end at 22 using 11 intervals. (Hint You
  may use Excel to sort the data first if you
  like).
• b) Graph the frequency histogram by hand.
• c) Graph the percentage polygon by hand.
• d) Make the cumulative percentage distribution by
  hand.
• e) Graph the ogive by hand.
• f) Check your answer for part a using Excel.
• 3) The data at http//www.cob.sjsu.edu/mease_d/hou
  ses.xls has house prices for a sample of 1500
  California homes. The prices are in thousands of
  dollars. Right click this link and select "Save
  Target As..." to download this file onto your
  computer. Then open it with Excel and use Excel
  to do the following. Be sure to print out your
  solutions and bring them with you to class for
  the quiz.
• a) Make the frequency distribution using Excel.
  Begin at 0 and end at 3.5 million using 7
  intervals.
• b) Graph the percentage histogram using Excel.
• c) Graph the percentage polygon using Excel.
• d) Make the cumulative percentage distribution
  using Excel.
• e) Graph the ogive using Excel.

3
Statistics for Managers Using Microsoft Excel
4th Edition

• Presenting Data in Tables and Charts

4
Chapter Goals

• After completing this chapter, you should be able
  to
• Create an ordered array
• Construct and interpret a frequency distribution,
  histogram, and polygon for numerical data
• Construct and interpret a cumulative percentage
  distribution and ogive for numerical data
• Create and interpret contingency tables, bar
  charts, and pie charts for categorical data
• Create and interpret a scatter diagram and a
  least squares regression line (in other chapter
  p. 387-398)
• Describe appropriate and inappropriate ways to
  display data graphically

5
The Cumulative Distribution
The cumulative distribution lists the total
percentage LESS THAN each class boundary It
starts at zero and ends at 100 The
corresponding polygon is called an ogive and uses
the class boundaries (NOT the midpoints)
6
In class exercise 16 Construct a cumulative
percentage distribution for the exam scores by
hand.
7
In class exercise 17 Construct a cumulative
percentage polygon (ogive) for the exam scores by
hand.
8
Ogives in Excel
These are done in Excel by Insert Chart
Line and then selecting line with markers
displayed at each data value The cumulative
percents should be the data range Under the
series tab at the top the category (X) axis
labels should be your labels for the
x-axis Provide a chart title and labels for
the X axis and Y axis If you have just one line
uncheck the show legend box under the legend
tab
9
In class exercise 18 Construct a cumulative
percentage polygon (ogive) for the exam scores
using Excel.
10
In class exercise 18 Construct a cumulative
percentage polygon (ogive) for the exam scores
using Excel. ANSWER
11
Grades and Missing Pictures
Current grades are now posted on the class web
page Always double check to make sure your grade
is correct If you were not here when I took
pictures last class, I lowered your grade
multiplier by 1 You can get these points back
if you either email me a picture or have me take
your picture sometime after class within the next
two weeks
12
You have 3 choices 1) Watch as it loads This
is done by simply clicking the link Advantage
No waiting Disadvantages No fast forward No
rewind pause works but stop ruins
everything
Hints for Watching Lecture Videos Using Windows
Media Player
13
2) Download first then watch This is done by
right clicking and selecting Save Target
As Disadvantage waiting for it to
download Advantages fast forward and rewind
work pause and stop work
Hints for Watching Lecture Videos Using Windows
Media Player
14
3) Start watching but then decide to download
half way through This is done by selecting
Save Media As from the File menu in Media
Player Advantage Dont have to wait as
long to download
Hints for Watching Lecture Videos Using Windows
Media Player
15
Tables and Charts for Categorical Data
With categorical data Instead of a frequency
distribution we make a summary table
Instead of a histogram we make a bar
chart or maybe a pie chart
16
Summary Table Example
Summarize data by category
Example Current Investment Portfolio
Investment Amount Percentage
Type (in thousands )
() Stocks 46.5
42.27 Bonds 32.0
29.09 CD 15.5
14.09 Savings 16.0
14.55 Total
110.0 100.0
(Variable is Categorical)
17
Bar Chart Example
Current Investment Portfolio
Investment Amount Percentage Type
(in thousands ) () Stocks
46.5 42.27 Bonds
32.0 29.09 CD 15.5
14.09 Savings 16.0
14.55 Total 110.0 100.0
18
Pie Chart Example
Current Investment Portfolio
Investment Amount Percentage Type
(in thousands ) () Stocks
46.5 42.27 Bonds
32.0 29.09 CD 15.5
14.09 Savings 16.0
14.55 Total 110.0 100.0
Percentages are rounded to the nearest percent
Savings 15
Stocks 42
CD 14
Bonds 29
19
In class exercise 19 Here are the class levels
(Freshman, Sophomore etc.) for all the students
in my two classes last semester. Make a summary
table by hand which gives the frequency for each
class level. JR JR JR SO JR JR SO SO SO JR SO FR
JR JR SO FR SR JR FR FR SR JR SR JR JR FR JR JR J
R SO JR JR SO SO SO SO SO SO JR JR JR SR SO SO JR
FR JR JR SO JR SO SO JR SR JR SO SO SO SO SR JR SO
JR FR JR JR SO JR JR JR SO JR SO FR SO JR SO SO S
O SO JR SR SO FR JR SO SO
20
Summary Tables Using Excel
To make a summary table in Excel, it is often
useful to use a Pivot Table to count the
frequencies of the different categories,
especially for large datasets. This is done by
selecting Data and then PivotTable and
PivotChart Report and dragging the name of the
column of interest into both the row and data
areas under the Layout menu. (Be sure you name
the column where you have the data first.)
21
Summary Tables Using Excel
22
Summary Tables Using Excel
23
Summary Tables Using Excel
24
Summary Tables Using Excel
25
Summary Tables Using Excel
26
Summary Tables Using Excel
27
In class exercise 20 Make the same summary
table as in ICE 19 now using a Pivot Table in
Excel.
28
In class exercise 20 Make the same summary
table as in ICE 19 now using a Pivot Table in
Excel. ANSWER
29
In class exercise 21 Construct the bar chart by
hand.
30
Bar Charts Using Excel
Once you have a summary table, you can use this
to make a bar chart by using Insert then
Chart then Column and using the Clustered
Column (the first choice). As before tell Excel
data range (the counts) and the Category X-axis
Labels (under Series). Dont use a legend
unless you are doing multiple groups (which we
arent now). Important dont try to use the
numbers straight from the Pivot Table paste
them somewhere else first and then use them
31
Bar Charts Using Excel
32
Bar Charts Using Excel
33
In class exercise 22 Construct the same bar
chart using Excel.
34
In class exercise 22 Construct the same bar
chart using Excel. ANSWER
35
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 (but we wont do this part)
• Used to separate the vital few from the
  trivial many (Pareto Principal)

36
Pareto Diagram Example
Current Investment Portfolio
invested in each category (bar graph)
cumulative invested (line graph)
37
In class exercise 23 Construct the Pareto
Diagram for class level using Excel.
38
In class exercise 23 Construct the Pareto
Diagram for class level using Excel. ANSWER