Homework 2
CS101A1
(due Wednesday, March 26, 2003)

 
1. Excel.  For this problem, you will manipulate a spreadsheet in Excel.  This assignment asks you to do things above and beyond what you have done in lab.  The idea is for you to extend your knowledge of how to use a spreadsheet program.  If you get stuck, consult the Excel Help menu or search the Internet.  You may not, however, consult with any person other than your T.F. or lecturer.
   Save your Excel work in a file called hw2-part1.xls
1. Download the following file from the course web page: students.xls
2. You will notice that students.xls is filled with formulas, in fact, random functions to produce the grades.  Please strip the functions out leaving just the values.  (Hint:  use "Paste Special").
3. 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)
4. Using formulas, calculate the two lowermost rows of the spreadsheet:
• Median Grade
• Average Grade
5. Change the formatting of the new cells so that each number has only one decimal place showing.
6. Change the formatting so that the cell widths match their content (use AutoFit).
7. Add border lines between the heading rows and the data, and between the raw data and the calculated values.
8. Sort the data by last name.
9. Make a new worksheet (not a new file) with the same data, sorted by grade.
10. Using Data Analysis Tools, create a histogram of the grades in chart format in a new worksheet tab.  You may need to do "Tools . . . Add-Ins" and check "Data Analysis Tools" to enable your version of Excel to do this.  If you don't know what a histogram is or how to make one, consult Excel help or search the Internet.
11. Using the Chart Wizard, create a chart of the median grades for each assignment (not including the averages or ultimate grade).
• For this chart create a column chart.
• Add a title to your chart.
• Add a legend to your chart; place the legend in the lower part of the chart.
• Make the chart in a new worksheet tab.

 
2. Book Problems.  Type up the answers in your favorite word-processor program. Call the file hw2-part2.doc
1. Page 98:  Exercise 3
2. Pages 161-62:  "ROBOT" Exercises 2, 13, and 15
3. Pages 225-26:  Exercise 7, 15

Submission.  Make a directory on your CSA account called hw2. Copy the files hw2-part1.xls and hw2-part2.doc into the directory and gsubmit the entire directory on or before the deadline.