# Excel+Histograms+and+Pie+Charts

Document Sample

```					State            Voter Turnout Percentage (2004)
Alabama                                        57.1
Arizona                                        57.4
Arkansas                                       53.9
California                                     59.2
Connecticut                                      64
Delaware                                       64.8
Florida                                        65.9
Georgia                                        57.1
Hawaii                                         48.3
Idaho                                          63.2
Illinois                                       59.9
Indiana                                        54.7
Iowa                                           69.3
Kansas                                         62.8
Kentucky                                         59
Louisiana                                      60.4
Maine                                          72.4
Maryland                                       63.5
Massachusetts                                  63.3
Michigan                                       66.4
Minnesota                                      76.8
Mississippi                                    55.6
Missouri                                       66.1
Montana                                        63.6
New Hampshire                                  70.3
New Jersey                                     64.1
New Mexico                                     57.7
New York                                         57
North Carolina                                 58.4
North Dakota                                   64.6
Ohio                                           66.4
Oklahoma                                       58.7
Oregon                                         69.9
Pennsylvania                                   62.2
Rhode Island                                   58.7
South Carolina                                 52.7
South Dakota                                   68.9
Tennessee                                      56.6
Texas                                          53.4
Utah                                           61.1
Vermont                                        65.8
Virginia                                         61
Washington      66.9
West Virginia   53.3
Wisconsin       76.2
Wyoming         64.4
Constructing Histograms
As an example of how to make a histogram in Excel, we will make a histogram of
voter turnout percentages for the 2004 presidential election.

1. Enter this data into Excel. This has already been done for you.

2. Next we will have to set the interval size, also known as the bin size.
To do this, first determine how many intervals you should use. Take the range of
your data (maximum value - minimum value) and choose how many intervals
would best convey the data. You should generally have 5 to 15 intervals. To find
the interval size, divide the range by the number of intervals you have chosen.
You can choose convenient integer or decimal values for the end points of the
intervals.

Maximum Value
Minimum Value

Range

Number of Intervals
Interval Size
Intervals

Put the larger endpoint of each subinterval in a column in Excel.
Note: the Excel function will start with the first value you enter and count all
values in the data set that have not already been counted and are less than or
equal to the number you have entered.

Endpoint
3. In Excel, go to the Data tab, and select Data Analysis and then Histogram.
Note: If Data Analysis is not there, go to the Excel button, then
Excel Options, Add-Ins, Go, and then check the boxes for
Analysis ToolPak and Analysis ToolPak -- VBA.
This function in Excel will create a table similar to the frequency tables we have
created in class.
a) For Input range, highlight your data set (here, it is voter turnout percentages).
b) For Bin range, highlight the bins.
c) For Output range, highlight a cell in your spreadsheet where you want your
output (the table Excel is creating). It is convenient to put this right next to your
column of bin endpoints. Click on OK.

Endpoint

4. The table that is generated by Excel gives you the Bins (endpoints) and
frequency (counts) for each subinterval (the number of data values in each
subinterval).

5. Convert the frequencies into relative frequencies by dividing each frequency
by the total number of data values.

Endpoint       Bin      Frequency Relative Frequency
6. Make a column of your desired x -axis labels. There are several ways to do this.
You can label them by the bins range or by the bins midpoint.

Labels     Bins           Bin     Frequency Relative Frequency

7. Using a table in Excel similar to the one above, make the relative frequency
histogram graph, using the "column" graph in the Excel chart wizard. To do this,
Select data, entering the relative frequencies as the Legend Entries (Series), and
the labels as the Horizontal Axis Labels. Make sure to label your axes and include
a proper title. The gap between the bars can be removed by right clicking on a
bar and selecting Format Data Series and selecting No Gap.

Histograms can be created similarly on your calculators. Try it!
Enter your data into Stat, Edit. Turn on Stat Plot by going 2nd Stat Plot. Set the
bins by going to Window and setting the Xmin, Xmax, and Xscl (bin width). Then
hit Graph.
Constructing Pie Charts
1. Start by completing the first four steps from Constructing Histograms.

Endpoint       Bin     Frequency

2. Using a table in Excel similar to the one above, make the pie chart, using the
"pie" graph in the Excel chart wizard. To do this, Select data, entering the
frequencies as the Legend Entries (Series), and the labels as the Horizontal Axis
Labels. Make sure to label your bins and include a proper title.

```
DOCUMENT INFO
Categories:
Tags:
Stats:
 views: 5 posted: 12/9/2011 language: Portuguese pages: 7