Homework 1, Math 320, Spring 2001

Name:____________________________

Math 320 Homework #1 --- Due 1/26

Begin the exposition of your work on this page. If more room is needed, continue on sheets of paper of exactly the same size (8.5 x 11 inches), lined or not as you wish, but not torn from a spiral notebook. Enter ``Section 1'' after your name if you are in the morning section (10AM-11AM) and ``Section 2'' for the noon section (12PM-1PM). You should do your initial work and calculations on a separate sheet of paper before you write up the results to hand in.

Output from Excel must have your name and the homework number in cell A1 (see below). Staple all sheets together. Unstapled papers will not be accepted.

1. Pick a random sample of 5 presidents from the list of presidents on page 20 of the text. Use the table of random digits on page 13 (Figure 1.1) beginning with the third line (82844...). For convenience, use only the first 4 digits in each group of 5 random digits, so that each group of 5 contributes two different trial 2-digit random numbers. Which presidents did you choose?

2. Do Problem 2.12 on page 47. Construct the histogram by hand. Use graph paper or plain white paper and a ruler. Label the axes and the class endpoints carefully. Round class widths up to the nearest integer.

3. Use Excel with the same data as in Problem 2 to produce the same histogram. In Problem 4, you will construct a Pie Chart. Since this problem and the next will be an introduction to Excel, we will give step by step instructions. (See also Notes about using Excel on the Math320 Web site.) If you have had no prior experience using Excel, it will take you longer to produce the histogram than if you did it by hand. However, after a few hours with Excel, you will begin to appreciate the help a computer can give you for doing data analysis.

(a) Go to a computer that has Excel on it. For example, all the computers in the Artsci Computer Lab in the basement of Cupples I have Excel. If you have a personal computer, it may have Excel already installed.

(b) Locate the Excel icon on the screen. (If the screen is dark, nudge the mouse. If you don't know what the mouse is, ask someone.) Double-click on the Excel icon. (That is, move the mouse pointer to that icon and click the left-hand button on the mouse two times quickly in succession.)

(c) When the spreadsheet grid appears, type your name in cell A1 (first and last) followed by a comma and then HW1. To enter text or numbers in a particular cell, first highlight that cell by clicking on it to make it the active cell. Change HW1 to the actual homework number in subsequent homeworks.

(d) Enter the data for problem 2.12 that appears on page 47 of the textbook starting with cell A3. Note that columns in Excel are denoted by letters (A,B,C,D,...) and rows have numbers. Thus cell A3 is the third cell in the leftmost column. Type in the numbers in five rows beginning with cell A3, just as the numbers appear in the text book. Use the Tab key to go to the next cell in a row and the Enter key to go to the first cell in the next row. Alternatively, the Right-Arrow and Down-Arrow keys can be used instead of Tab and Enter. Your last entry, an 11, will go into cell J7.

NOTE: If you purchased a new copy of the textbook, the data is also on a diskette that came with your textbook. For later problems, you may want to enter the data from the diskette directly into the spreadsheet. In this case, the data for the problem would be imported as 50 numbers in a single column. It would take about as much effort to get the data in the form as it appears in the textbook as it would to enter the data by hand. See Importing Data from the Textbook Data Disk for more details.

(e) Consider the class or bin endpoints that you found in part (b) of problem 2.12. Enter the text ``Bin boundaries'' in cell A9 and then enter the right-hand bin boundaries into cells C9 to G9. Since there are five bins and six class endpoints, this will take five cells. Start with cell C9 instead of B9 so as not to obscure part of the text ``Bin boundaries''.

(f) On the top menubar, click on Tools, go to the bottom of the dropped down list, and click on Data Analysis. If Data Analysis does not appear on the Tools menu, then see Notes and Warning below or the Data Analysis Toolpak in Excel on the Math320 Web site. A dialog box that says Data Analysis should then appear. (The technical name for this kind of window is ``dialog box'', because you use it to communicate with Excel.) In the dialog box, either double-click on Histogram or else click Histogram once and then click the OK button. In general, we will write such a sequence of mouse clicks as (for example):

Tools | Data Analysis | Histogram | OK

Notes:   If Data Analysis DOESN'T APPEAR on the Tools drop-down list, click on Add-Ins... instead. On the list of Add-Ins Available, click to put a check in the box preceding Analysis ToolPak. If there is an entry for Analysis ToolPak - VBA, then check it as well. Then click OK and wait for Excel to finish. Now go back to Tools and follow the above instructions for Data Analysis.
Warning:   If Data Analysis appears on the Tools drop down list, but Analysis ToolPak - VBA is not checked in the window that appears when you click on Tools | AddIns..., then the Histogram function may not work properly. In that case, enter Tools |  Add-Ins..., check the box before Analysis TookPak - VBA, and then click OK. After Excel is done, go back to Tools and follow the above instructions for Data Analysis.

(g) At this point the Histogram dialog box should appear on the screen waiting for you to fill in the blanks. Namely,

(h) Two items should now appear on the screen: A table with bin counts and the histogram itself. If the bin-count table appears but the histogram does not appear, see Warning above.

You can move the histogram around by clicking on the histogram, holding the mouse button down, and moving the mouse. (This is called ``dragging''.) Fiddle a bit to move the histogram underneath the bin-count table, edit the labels on the histogram, or resize the histogram. If you click on the histogram, 8 small black boxes should appear on the edges. These can be used to resize the histogram. Alternatively, move the cursor to a corner of the histogram box and the cursor should change into a double arrow. Click and hold and you can stretch the histogram box sideways and down to make it as large as you like.

(i) You can also move the histogram, or any block of cells, by ``Cutting and Pasting''. To do this, (a) click on the histogram or use the mouse pointer to highlight a block of cells, (b) click on the Copy button on the second icon menubar (it looks like two 8 1/2 by 11 sheets of paper with the upper-right-hand corners folded; alternatively, you can click on Edit on the main menubar and then Copy), (c) move the mouse to where you want the upper-left-hand corner of the block or histogram to go, and (d) click on the Paste button on the second menubar just to the right of the Copy button (or enter Edit | Paste). A copy of the histogram or block of cells will now appear at the current mouse-pointer location.

Note: You can find out what icons on the menubars do before you click on them by moving the move pointer to them and not clicking the mouse button. After about a second, a short description of that button should appear.

(j) If you make a mistake or change your mind, you can usually ``Undo'' one or more previous steps. Click on the ``Undo'' button on the second icon menubar (this looks like an arrow pointing counter-clockwise). There is also a ``Redo'' button in case you change your mind about an ``Undo''.

4. Continue using Excel to produce a pie chart using the same data.

(a) In addition to the histogram, you should also see a table in the spreadsheet with column headings ``Bin - Frequency - Cumulative %''. The right-hand boundaries of the histogram cells should be listed under the column headed by ``Bin''. There should be an additional row with ``More'' as a row heading. The bin counts should be listed under ``Frequency''. If the Frequency entry for More is nonzero, then you have set your bin boundaries incorrectly. All values smaller than the first bin boundary are put into the first bin, so that there is no ``More'' entry for values to the left of the bins. If the bin-count table is wrong, then you should redo the histogram.

If a spreadsheet column is too narrow, then part of a column may be obscured. For example, you may only see ``umulative'' instead of ``Cumulative %''. You should increase the width of a column so that you can see all of the text in that cell. Click on the cell that says ``umulative'' to highlight it and enter
Format | Column | AutoFit Selection
That entire column will now become wide enough to see all of ``Cumulative %''.

(b) You can use the Excel Chart Wizard with the ``Bin - Frequency - Cumulative %'' table to create a large variable of graphics. Click on the Chart Wizard icon on the icon menubar, or else click on Insert and then Chart... (The Chart Wizard icon has three brightly-colored vertical bars.)

When the Chart Wizard dialog box appears, note the large variety of types of graphics available. (Note: ``wizard'' is a technical term for a series of dialog boxes that appear at the same location with buttons to go between them. Many Windows programs use ``wizards'' to carry out tasks that are too complicated for a single dialog box. The ``Cancel'' ``Back'' ``Next'' ``Finish'' buttons at the bottom of the Chart Wizard are characteristic of wizards in general.)
The first ``Chart type'' option in the Chart Wizard (``Columns'') are histograms of the sort that you just created using the Histogram function. The reason that the approximately six sample histograms look more complex is that they are displaying several different sets of values on the same graphic. For example. you could show histograms of heights of two sexes or sales histories for four types of products on the same plot. The Excel term for each set of plotted values is a ``series''. Thus a combined histogram for four product types would have four series. The histogram that you just constructed from the Histogram dialog box has two series, one for counts and one for cumulative percentage.

(c) Click on Pie for Pie Chart in the first dialog box of the Chart Wizard. Highlight the Pie Chart style that you prefer. The standard Pie Chart style is the upper left choice. Click on Next. A dialog box with an entry for Data Range should appear.

If the ``Bin - Frequency'' column headings are in cells B11:D11, then the five bin labels should be in cells B12:B16 and the five bin counts in C12:C16. Enter C12:C16 in Data range and click on the open circle next to Columns. A Pie Chart can only use data from one series. If you check Columns, some versions of Excel construct a Pie Chart from the first column in the Data range. Thus if you entered B12:C16 for the bin boundaries plus counts or B12:D16 for the entire bin data table, then Excel would construct a Pie Chart from the bin boundaries (4,8,12,16,20). This would produce an attractive Pie Chart but would not be particularly informative. If the version of Excel that you are using can use the first column in Data Range as labels, then do it that way instead. Just make sure that the Pie Chart has accident counts and not bin boundaries.

(d) Click on Next to go to the third dialog box. If your version of Excel permits, click on Titles and enter ``Accidents per Week'' under Chart Title. Also, click on Data Labels and put a check next to Show values. The latter option puts numbers on the graphic just outside the pie slices. You can tell from this whether you are illustrating accident counts or bin boundaries. Click on Finish. A Pie Chart with the proper labels should now appear on your spreadsheet. Use the cursor to move it below the histogram.

(e) Check the worksheet that you have been working on by clicking

File | PrintPreview
This displays how it will appear when it is printed. Click on Close to return to your worksheet. If you are satisfied with it, then click on
File | Print...
If not, go back and work on it some more. If the 10 data columns are too wide to fit on one page, then you can make them narrower by entering
Format | Column | Standard Width...
and then entering a smaller number. You should be able to get everything on a single page of output.

(f) When you are satisfied with what you have and you have printed it, click on File | SaveAs... and save it to the floppy disk you should carry with you to save all your Math 320 computer work on. It will ask you to assign a name to the file. Something like a:HW1 will work for saving it to your floppy as file HW1. It will automatically add the appropriate three-letter extension for Excel files.

(g) Suppose you goofed and it's not what you want. If it's just the histogram that needs to be redone, do that and give the same cell as before for the Output Range. The old histogram will be erased and replaced by the new one. If something more has to be changed, you can go to a new sheet.

Notice the file tabs at the bottom of the spreadsheet screen. You have been working on sheet one. Click on sheet two, and you get a fresh spreadsheet. If for some reason you wanted to copy the data to the new sheet, then do so. (See Problem 2(i) above.) Even the graphs can be moved in this way. When you are done, don't forget to save. It will be the same file, but now with Sheets 1 and 2. You can print Sheet 2 by clicking on the Sheet 2 tab and then clicking on File | Print.

One advantage to using new sheets is that all your work on HW1 will be in a single file. Different ways of doing things are sometimes worth keeping for later reference.