LAB SESSION 2
GRAPHIC
PRESENTATION OF UNIVARIATE DATA
INTRODUCTION: Graphically representing data is one of the most helpful ways to become acquainted with the sample data. In this lab you will use Excel to present data graphically. You will be analyzing data using four types of graphs: Circle graphs, Bar graphs, histograms, and cumulative (relative) frequency plots (ogives).
GRAPHIC PRESENTATIONS OF DATA
There are several ways to display a picture of the data. These graphical displays help us get acquainted with the data and to begin to get a feel for how the data is distributed and arranged. In attempting to get a pictorial representation of data, we must decide what type of graphic display would best present the data and their distribution. The type of display used depends, in large part, on the type of data and the idea to be presented.
GRAPHIC DISPLAYS FOR QUALITATIVE (CATEGORICAL) DATA
CIRCLE GRAPHS
A circle graph shows the amount of data that belongs to each category as a proportional
part of a circle. Consider Exercise 2.11. Let’s begin by entering the data into a worksheet.
Place the expense category in one column and the amount in a second.
|
|
A |
B |
|
1 |
Expense
Category |
Amount |
|
2 |
Doctor's
personal income |
55.6 |
|
3 |
Nonphysician
personnel |
15.7 |
|
4 |
Office
expenses |
10.9 |
|
5 |
Medical
supplies |
4 |
|
6 |
Malpractice
insurance premiums |
3.5 |
|
7 |
Employee
physicians |
2.3 |
|
8 |
Medical
equipment |
1.5 |
|
9 |
All other |
6.5 |
Choose: Chart
Wizard > Pie > 1st picture > Next
Enter:
Data Range: (A1:B9 or select cells)
Check: Series in: columns > Next
Choose: Titles
Enter:
Chart title: How patients’ fees are used
Choose: Data labels
Select: Percentage
> Next > Finish
BAR GRAPHS
A bar graph shows the amount of data that belongs to each category as proportionally sized
rectangular areas. Let’s continue to use the data from Exercise 2.11, and present this data as a bar graph. Since we already have the data entered we can go right to the commands to create the bar graph:
Choose: Chart Wizard > Column
> 1st picture > Next
Choose: Data range
Enter: Data range: (A1:B9 or select cells)
Select: Series in: Columns > Next
Choose: Titles
Enter: Chart title: How patients’ fees are used
Category (x) axis: Expense
category
Value (y) axis: Amount
($) > Next > Finish
PARETO DIAGRAMS – A Special Type of bar graph
Consider Exercise 2.5. We are instructed to construct a Pareto diagram in this instance since this a quality control application. In constructing a Pareto diagram for Exercise 2.5, basically we are doing a bar graph, but sorting the data first. After you have input the categories into column A and the corresponding frequencies into column B, then continue with:
Choose: Data > Sort > Sort
by: Column B
Select: Descending > My
list has: Header row > OK
Then continue with the commands necessary to create the bar graph.
Note: Excel does not include the line graph
GRAPHICAL DISPLAYS
FOR QUANTITATIVE DATA
DOTPLOTS
Dotplots are a quick and efficient way to get a preliminary understanding of the distribution of your data. The dotplot display is not available, but the initial step of ranking the data can be done. Input the data into a column,
Choose: Data > Sort
Enter: Sort by: Column A (or whatever column the data is in
Select: Ascending
> My list has: Header
row or No Header row
Use the sorted data to finish constructing the dotplot.
STEM AND LEAF DISPLAY
The stem-and-leaf diagram is not available with a standard version of Excel. However, Data Analysis Plus (a collection of statistical macros for Excel) can be downloaded onto your computer from your Student Suite CD.
To illustrate the commands necessary to construct a stem-and-leaf display, let's use the data from Exercise 2.6 (points scored). Enter the data into column A with a heading in cell A1, then continue with:
Choose: Tools > Data Analysis Plus > Stem and Leaf Display >
OK
Enter: Input Range : (A2:A17 or select cells)
Increment: 10 (the stem increment
you wish to use)
|
Stem
& Leaf Display |
||
|
|
|
|
|
Stems |
Leaves |
|
|
3 |
->6 |
|
|
4 |
->6 |
|
|
5 |
->124456 |
|
|
6 |
->0111468 |
|
|
7 |
->1 |
|
Notice, originally the macro chose an increment of 10. If you click the down arrow for the increment box, note the different increment options. None of the other increments make sense for this particular data set.
HISTOGRAMS
Histograms are more useful for large sets of data. We expect the histogram of a sample to be similar to that of the population. To illustrate the many options under the HISTOGRAM command, let's use the data in Exercise 2.44 (on the Student Suite CD). The HISTOGRAM command separates the data into intervals on the x-axis and draws a bar for each interval whose height, by default, is the number of observations (or frequency) in the interval.
Input the data into column A (or retrieve data worksheet from the Student Suite CD). Input the upper class limits into column B (this is optional, but recommended).
|
|
A |
B |
|
1 |
GolfScor |
|
|
2 |
69 |
67.9 |
|
3 |
73 |
68.9 |
|
4 |
72 |
69.9 |
|
5 |
74 |
70.9 |
|
6 |
77 |
71.9 |
|
7 |
80 |
72.9 |
|
8 |
75 |
73.9 |
|
9 |
74 |
74.9 |
|
10 |
72 |
75.9 |
|
11 |
83 |
76.9 |
|
12 |
68 |
77.9 |
|
13 |
73 |
78.9 |
|
14 |
75 |
79.9 |
|
15 |
78 |
80.9 |
|
16 |
76 |
81.9 |
|
17 |
74 |
82.9 |
|
18 |
73 |
83.9 |
|
19 |
68 |
|
|
20 |
71 |
|
|
21 |
72 |
|
|
|
... |
|
Choose: Tools > Data Analysis** > Histogram > OK
Enter: Input Range: (A2:A147
or select cells)
Bin Range: (B1:B18 or select cells)
Select: Output Range
Enter: area
for freq. distribution, & graph: (C1 or select cell)
Select: Chart Output
**If Data Analysis does not show on the Tools menu:
Choose: Tools > Add-Ins
Select: Analysis ToolPak
Analysis ToolPak-VBA
To remove gaps between bars
Click
on: Any bar on graph
Click on: Right
mouse button
Choose: Format
Data Series > Options
Enter: Gap width: 0
To edit histogram:
Click on: Anywhere clear on the chart
-use handles to size
Any title or axis name to change
|
67.9 |
Frequency |
|
68.9 |
9 |
|
69.9 |
2 |
|
70.9 |
5 |
|
71.9 |
10 |
|
72.9 |
22 |
|
73.9 |
17 |
|
74.9 |
28 |
|
75.9 |
17 |
|
76.9 |
9 |
|
77.9 |
9 |
|
78.9 |
9 |
|
79.9 |
4 |
|
80.9 |
1 |
|
81.9 |
1 |
|
82.9 |
1 |
|
83.9 |
1 |
|
More |
0 |
Note that the upper class limits appear in the center of the bars. Replace with class midpoints.
Also note that if the data is already tabled, the commands are different. See your text.
OGIVES
To construct an ogive, the class boundaries must be in listed in column A and the cumulative percentages listed in column B. Let's use Exercise 2.52 in your text as an example. We are presented with a grouped frequency distibution.
Now you need this same information presented as a cumulative relative frequency distribution:
Class Cumulative
Boundaries Relative Frequency
0 <= x < 4 4/50, or 0.08
4 <= x < 8 12/50, or 0.24
8 <= x < 12 20/50, or 0.40
12 <= x < 16 40/50, or 0.80
16 <= x < 20 46/50, or 0.92
20 <= x < 24 49/50, or 0.98
24 <= x <= 28 50/50, or 1.00
Now you are ready to enter the class boundaries in column A and the cumulative percentages in column B. Be sure to enter 0 for the percent paired with the lower boundary of the first class and pair each cumulative percentage with the class upper boundary.
|
|
A |
B |
|
1 |
upper
class boundaries |
cumulative
relative frequencies |
|
2 |
0.9 |
0 |
|
3 |
3.9 |
0.08 |
|
4 |
7.9 |
0.24 |
|
5 |
11.9 |
0.4 |
|
6 |
15.9 |
0.8 |
|
7 |
19.9 |
0.92 |
|
8 |
23.9 |
0.98 |
|
9 |
28 |
1 |
then continue with:
Choose: Chart Wizard > Line > 4th
picture (usually) > Next
Choose: Series > Remove (remove all columns except column B)
Enter: Name: (B1 or select name cell
- cum. rel. freq.)
Values: (B2:B9 or select cells)
Category
(x) axis labels: (A2:A9 or select
cells) > Next
Choose: Titles
Enter: Chart title:
KSW Test Score
Category
(x) axis: Score
Value
(y) axis: Cumulative Relative
Frequency > Next > Finish
ASSIGNMENT: