MATH 1070 — EXCEL HISTOGRAM
Note:
First make sure that the “addins” for “Data Analysis” are available on your computer; click on “Tools”; if
“Data Analysis” is not on the menu, then click on “AddIns”; then check “Analysis ToolPak” and
“Analysis ToolPak VBA”; then click “OK”.
[If the addins are not available on your computer, you can
use a bar graph and turn it into a histogram, but you need to do the frequency table yourself.]
1.
Make sure that your data values are in Column A.
Their location will go in the “Input Range”position.
2.
Decide on the intervals that you want to use in your frequency table.
(The “default” choice of intervals
usually produces a poor result.)
Excel uses the following convention for the interval definitions (e.g. for
data in the range of 22 – 36):
2
0
<
x
≤
25
2
5
<
x
≤
30
30 < x
≤
35
35 < x
≤
40
3.
Put the
righthand endpoints
(here, 25, 30, 35, and 40) in another column next to your data (e.g.,
Column C).
Their location will go in the “Bin Range” position.
4.
Go to the “Tools” menu.
Click on “Tools”; “Data Analysis”; “Histogram” .
a)
Click on the “Input Range” position (space).
Then highlight (select) the data values in Column A.
b)
Click on the “Bin Range” position.
Then highlight the righthand endpoints in Column C.
c)
Click on the “Output Range” button.
Then click in the “Output Range” position.
Then click
somewhere on the worksheet.
That will be the upper lefthand corner of your output (which will
consist of a frequency table and the histogram graph).
[If you don’t do this, your output will be
on a separate page.]
You can move these items around later if you wish.
d)
Check the “Chart Output” option.
e)
Click on “OK”, and your output should appear.
5.
Now you need to “edit” your results, so that they look like an appropriate frequency table and histogram, as
follows:
a)
Change the label on the frequency table from “Bin” to a more appropriate label.
b)
Click on the chart (graph).
An outline with “handles” should appear.
Use the handles to make the
graph larger.
c)
If you don’t want the “legend” on the right side of the graph, rightclick on it and then click “clear” to
delete it.
d)
Rightclick on one of the bars, then click on “Format Data Series”, then click on the “Options” tab,
then change the “Gap Width” to zero (0).
e)
Still in the “Format Data Series” panel, you can now change the label on the horizontal axis from
“Bin” to something more appropriate.
You can also change the font size for both axes to make
the graph look better. Other editing options are also available.
f)
If you don’t care about the blue and gray colors, you can also change them to white, and the
histogram will print out faster.
To change the blue bars, rightclick on a bar, then click on
“Format Data Series”, then click on the “Patterns” tab, then click on the desired color.
For the
gray area, rightclick on the gray area, then click on “Format Plot Area”, then “Patterns”, then
click on the desired color.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
This is the end of the preview.
Sign up
to
access the rest of the document.
 Summer '08
 AKBAS
 Math, Statistics, Normal Distribution, Statistical hypothesis testing, tail test

Click to edit the document details