Econ 101

How to create a Histogram (Practice)

For the practice portion of this lab, we will be using the excel filed called “Week 3 Practice Data.xlsx”

When we have quantitative data, it makes more sense to create a histogram to show the relative frequency of different values. Excel has add-in features to draw histogram, but we have better control of the result when we construct our own. We are going to use the bar graph tool to construct ahistogram.

1.   Goto the sheet labeled “Number Data” . The 200 data values ae located in the range A2:A201. The range of the data values is a minimum of 43 and a maximum of 500. The page is missing the mean of the values. Type “=AVERAGE(A2:A201)” into cell D3.

2.   The page is also missing the standard deviation of the values. Type “=STDEV(A2:A201)” into cell D4.

3.   Two of the bars are missing in the histogram. That’s because the associated rows in the Frequency Chart are missing. Count the number of values in the data that are in the range [54, 66] and place the count into cell D15.

4.   In cell E15 place the formula for the percentage “=D15/D21” .

5.   Excel can count for you. Place the following into cell D16: “=COUNTIF(A$2:A$201,"<=79") -COUNTIF(A$2:A$201,"<=66")” .

6.   In cell E16 place the formula for the percentage “=D16/D21” . Make sure that all 200 values are accounted for.

Sometimes there are extreme values in the data, which makes it very tedious to divide all possible values into intervals. We can group together all of the very high values (200 and 500) into the last bin by defining the last interval as “greater than 118.”

Lab Assignment

1.   Open the spreadsheet entitled “Econ 101 Survey Results.” Create a frequency table of the number of hours worked per week (Column C). The intervals for hours worked should be: 0, 1-5, 6-10, 11-15, 16-20, 21-25, 26-30, 31-35, 36-40, greater than 40. Include counts and percentages in the frequency table. Create a histogram using the frequency table you created that displays percentages on they-axis.

2.   Create a frequency table for the number of hours worked per week, but only for students who worked (hours worked are not zero). The intervals for hours worked should be: 1-5, 6-10, 11-15, 16-20, 21-25, 26-30, 31-35, 36-40, greater than 40. Include counts and percentages in the frequency table. Create a histogram using the frequency table you created that displays percentages on they-axis.

3.   Transfer the tables and charts you created in the previous steps into a word document. Number your tables and charts according to the order in which you place them in the document (“Table 1: Frequency table for XX”, “Figure 1: Histogram for XX” etc.).

4.   In one paragraph, describe the information contained in your tables and charts. Some questions you should address in your writeup:

a.   What percentage of students in the class work at all? What percentage of students work more than 20 hours? More than 40 hours?

b.  For all students in the class who worked, what percentage work more than 20 hours? More than 40 hours?

c.   For all students in the class who worked, what is the most frequent interval of hours worked?

d.  Why are the percentages different in the frequency tables created in step 1 versus step 2? Which do you think it is important to report both?

Submit your document (tables, charts,  and paragraph) through Canvas by Sunday, 11:59pm.