Instruction for exercise 2
For this exercise you need to use the "Data Analysis" module in Excel. From the pull-down menu, click on Tools, then choose "Data Analysis..." (located at the very bottom of those
choices). If you cannot find the "Data Analysis..." option, it is quite possible that you have not
activated this module in your computer. Click on Tools, choose "Add-Ins...", and then check
"Analysis ToolPak" and click OK.
Exercise 2 asks you to produce a histogram of the data sample. Histogram is an effective
graphing method to display the distribution of the data value in large data sets. The data for the
histogram comes from a frequency table which shows the number of observations that fall into
each of the subintervals or data ranges (called bins in Excel). For example, for the income data
in Question 1, you might want to construct a frequency table to look at how people have the
income in $20K-$25K range, how many people in $25-$30K range, ...
- Before creating a frequency table, you need to define these bins or income ranges you
want to look at.
- First figure out the width of the bins (normally equal in size) to eventually allow
for 5-15 bins for the whole income range (the higher the number of bins, the more
informative the graph is going to be).
- Choose an upper limit for the first bin, so that the smallest data value in the data
set falls sightly below this upper limit.
- Given the bin width (e.g., $5K in Question 1), determine the upper limits for all
bins needed to include all the data.
- After you create a list of numbers which specify the limit of each bin or range, you can
create a histogram directly in Excel.
- Choose Tools - Data Analysis ... - Histogram
- From the Histogram dialogue box, click on the blank space beside "Input Range",
then, use your mouse to select all the original income data. This will allow the
address range to be specified in the "Input Range" box. You can also type in the
address range (e.g., A2:J26).
- Same way to enter Bin Range. Specify the address of the list of bins you just
- Output options. You can choose to put the result in a New worksheet or put in the
current worksheet. In that case you have to specify the "Output Range" by
providing the address of the top left corner of the future destination of the output.
- Check Chart Output (at the very bottom of the menu) to get a histogram. If, by
any chance, Excel fails to plot the chart, you can plot the histogram yourself, using
the data from the frequency table (the similar plot has been done in our Excel
exercise last week).