Chapter 2/Statistics: Principles and Methods; Johnson
Organization and
Description of Data
Excel is extremely versatile when it comes to organizing data. However, its versatility comes at a
price; it is up to the user to accurately enter the data, use correct formulas and organize it in a
manner that properly conveys the data. In addition, Excel is not as obvious as other statistical
programs. Its major limitation is the imagination of the user, who has to determine how best to
input the data.
Excel has numerous options for the displaying of data in various graphs and plots, but many
statistical charts, like Dotplots, are not available. Histograms may be created, but to make a true
histogram the user must manipulate the default bar graph quite a bit. Again, it is up to the user
to properly create plots that are not misleading.
This chapter includes an introduction to the Data Analysis ToolPak, which is a free Excel Addin.
In order to use the Data Analysis ToolPak
, you’ll n
eed to add it to your
Data
Tab. To do so, select
the
Microsoft button
then click on
Excel Options,
then
Addins
and then
Analysis Toolpak
, and
click
Go.
Then check the
Analysis Toolpak
and click
OK.
The Toolpak may disappear at times. If
this happens you have to repeat the process. However, before you try to add it again, make sure
you unselect Data Analysis ToolPak first and click okay. Then repeat the process described
above.
Calculating Relative Frequency
Table 1 (Summary Results of an Opinion Poll), which accompanies Example 1, on Page 25 of the
text
shows the tallies and relative frequencies of the responses of student’s opinions on
dormitory regulations.
Create a table in Excel as shown below.
Chapter
2
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Excel Manual
To make the table format automatically, select the
Format as Table
option in the
Styles
Group
of the
Home Ribbon
as shown below.
Select cell
B2.
Enter the formula
=SUM(B2:B4)
. This can be done in numerous ways. You can
type it in directly, select the
AutoSum
button in the
Editing
Group of the
Home
Ribbon, or you
can go to the
Formula
Ribbon and click on the
AutoSum
option in the
function library
group.
To calculate the relative frequencies enter the formula
=B2/$B$5
in cell
C2
. Since this is a table,
the remaining cells in the column will automatically fill with the formula. Click on the comma
format button to get two decimals and then click on the increase decimal button to make it
three decimal places. These are in the
Home
Ribbon within the
Number
group.
Note that by using the “Format as Table” feature
Excel begins to make some assumptions and
does some things automatically for you. You may be more comfortable not using this feature
and doing the formatting yourself. If you choose to format the table yourself, you will have to
autofill the formulas also.
Formulas are shown below:
This is the end of the preview.
Sign up
to
access the rest of the document.
 Fall '11
 Chen
 Statistics, Formulas, Frequency, Frequency distribution, Bar chart, Histogram

Click to edit the document details