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 dataand 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 theamount 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 dotplotdisplay 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 Exercise2.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: