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, ...

  1. Before creating a frequency table, you need to define these bins or income ranges you want to look at.
    1. 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).
    2. Choose an upper limit for the first bin, so that the smallest data value in the data set falls sightly below this upper limit.
    3. Given the bin width (e.g., $5K in Question 1), determine the upper limits for all bins needed to include all the data.
  2. After you create a list of numbers which specify the limit of each bin or range, you can create a histogram directly in Excel.
    1. Choose Tools - Data Analysis ... - Histogram
    2. 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).
    3. Same way to enter Bin Range. Specify the address of the list of bins you just created.
    4. 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.
    5. 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).