For this problem, you will manipulate and print a spreadsheet in Excel. Where indicated, print out your results.  Attach all pages to your homework and staple.

1. Download the following file: raw_data.xls
2. Note that the numbers change every time you press the F9 function key.  Save the file three times under the "CSV (Comma delimited)" format with three different filenames.  (Say "yes" to the dialog box asking whether you want to keep the spreadsheet in Excel format.)  Press the F9 key before each time you save.  Each file should thus have different numbers in it.  Name the files as follows:
• students1.csv
• students2.csv
• students3.csv
3. [Comment:  Not only should you have three files with different numbers in them, but your files should be different from every other student's files.]
4. Exit Excel, saying "no" to the dialog box asking whether you want to save changes.
5. Now open all three CSV files.  In one of the Excel windows, insert two new worksheets (you should have two additional tabs on the bottom of the window).  Cut and paste from the other windows, so that you end up with one file containing three worksheets.  Save this file as a "Microsoft Excel Workbook" under the following name:  students.xls
6. Rename the worksheets as "Course 1," "Course 2," and "Course 3."
7. For each worksheet, perform the following steps:
1. Using formulas, calculate the three rightmost columns of the spreadsheet:
• Average Quiz Grade
• Average Homework Grade
• Grade (as determined by the Syllabus in this course—however, do not drop the lowest quiz grades)
2. Using formulas, calculate the two lowermost rows of the spreadsheet:
• Median Grade
• Average Grade
3. Change the formatting of the new cells so that each number has only one decimal place showing.
4. Change the formatting so that the cell widths match their content (use AutoFit).
5. Add border lines between the heading rows and the data, and between the raw data and the calculated values.
6. Sort the data by last name.
7. Using Data Analysis Tools, create a histogram of the grades in chart format:
1. From the "Tools" menu, select "Add-Ins." [only need to do this once]
2. Select "Analysis Toolpack" and click "O.K." [only do this once]
3. Fill an empty column to the right of the worksheet data with the numbers from 0 through 100 to use as your "Bin."  Put a heading on the column so naming it.
4. Make the chart a separate worksheet.
8. [Reminder:  You might want to save the file at this point.]
9. Clean up the chart:
1. Resize it so that it fills the whole screen.
2. Delete the legend box on the right.
3. Rename the title, "Course 1."  [or "Course 2" or "Course 3," as appropriate]
4. Rename the X Axis, "Grades."  Format the X Axis to font size 9 with 5 categories between tick-mark labels.
5. Rename the Y Axis, "Students."  Format the Y Axis to font size 9.
6. Name the worksheet containing the chart, "Course 1 Histogram."  [or "Course 2 Histogram" . . .]
10. Go back to the data worksheet (either "Course 1" or "Course 2" or "Course 3").  Using the Chart Wizard, create a chart of the median grades for each assignment (not including the averages or ultimate grade).
1. For this chart create a column chart.
2. Add a title to your chart.
3. Add a legend to your chart; place the legend in the lower part of the chart.
4. Make the chart a new sheet entitled "Course 1 Medians."  [or "Course 2 Medians" . . .]
5. Format the Y Axis to font size 9 with maximum scale 100.
6. Using your discretion (along the lines above), "clean up" the chart for legibility and readability.
8. Make sure you save your work.  Indeed, I recommend making a backup copy on a diskette.
9. At this point, you should have a single file, students.xls  The file should contain three data sheets called "Course 1," Course 2," and "Course 3."  There should be three graphs entitled "Course 1 Histogram", "Course 2 Histogram," and "Course 3" Histogram.  There also should be three graphs entitled "Course 1 Medians," "Course 2 Medians," and "Course 3 Medians."
10. Create a new file using Microsoft Word.  From the "File" menu, select "New ..." and choose a Memo format (you may choose to use the memo wizard).  Write a short memorandum (100 words or so), discussing your thoughts on the results:  How much variation was there between the different medians?  Were the histograms similar or different?  Why?
11. The memorandum should be addressed to me from you with an appropriate date and subject line.  Save it as memo.doc
12. Submit the following files in WebCT for "Homework 1":
1. students.xls
2. memo.doc
3. students1.csv
4. students2.csv
5. students3.csv