Hwk 4 (due Tue. 5/17/11)
(1)Textbook: 1.70, 1.89, 1.92, 1.94
(2)Excel work (only turn in the templates)
(2a) Input data from the Excel file ``2004_CAR’’ at the Blackboard site, and draw side-by-side
boxplots for city vs highway. Use the ``rule of thumb’’ to identify possible outliers. Hand in the
single-page template.
Note:
Creating boxplots requires you to have the WHFStat Add-In software, available in StatsPortal that you don’t have an access to, hence you are allowed to do (2a) by hand. Still, you need to make your boxplots as accurate as you can. In particular, mark all important values on the boxplots: min, Q1, M, Q3, max, any possible outliers.
(2b) Read Excel Manual page 25-28 about histograms. Follow the instruction to add and activate
``data analysis toolpak’’ if necessary. Input data from the Excel file ``ta01_007’’
(Table 1.7 in the textbook) at the Blackboard site, then do the following:
We only need the data in cells B1:B51 (just delete the ``reading’’ in column C). Create a histogram.
Begin by entering the bin values (62, 63, ..., 67) in cells C2:C7, below the column title "My Bins" in cell C1. The "Output Range" should be D1, so you will automatically get from Excel an additional column with the title "Bin" in column D. Modify your histogram: remove the legend, close all gaps between bars, include one decimal place in the horizontal axis values (62.0, 63.0, etc.Note:for Microsoft Excel 2010 you can right click on the axis and go to 'select data'. On the right side you can click 'edit' and format the 'horizontal axis'), label the axes as "Temperature" and "Count", give the chart the title "Frequency Histogram of Mean Annual Temperatures in Pasadena", exclude the "More" bin from your picture (but it should remain in row #8 of the spreadsheet). Now delete the "Year" data in column A, so everything remaining should shift by one column to the left. Re-size and move the chart to occupy cells B9:F27.
Now select the cells B9:F27 and copy the entire chart into cells B29:F47 (for the moment, you have two identical charts on your worksheet). In cell E1, put the title "Percent", and then create (in cells E2:E7) the appropriate percentages corresponding to the bins and frequencies. Select only the chart in cells B29:F47, and convert it into a histogram of percentages (re-label the vertical axis as "Percentage", and re-title this chart as "Percentage Histogram of ...").
As usual, be sure to adjust all widths so that every entry (titles, names, numbers, etc.) is completely visible.
** Template to hand In for (2b):
Print your Excel spreadsheet (only one page); it should show the data in column A (although the bottom of the data-list may not fit on this single page -- that's OK, do not print a second page); it should also show the bins, frequencies, percents (in columns B, C, D, E), and the 2 histograms in B9:F27 and B29:F47. In your page set-up for printing, be sure to: use "portrait" orientation and include "row and column headings". As usual, include gridlines on the spreadsheet (outside of the chart areas).