CSCI 101: Intro to Computer Science

Lab 2.2:   Creating a Spreadsheet

1. Open the Microsoft Excel program now and follow the steps below to define a ledger to keep track of a bowling team's weekly scores. Imagine a team of five players, each of whom bowls three games per week.
   1. Enter three scores for player 1 into cells Al, B1, and C1.
   2. Enter a formula for player 1's total score (Al + B1 + C1) into cell D1.
   3. Enter the formula for player 1's average score (D1 / 3) into cell El.
   4. Change one of player 1's scores and verify that the total and average values change correspondingly.
   5. Enter the scores for players 2-5 into columns A, B, and C of rows 2-5, respectively.
   6. Enter appropriate formulas for each player's total and average scores into columns D and E. (Note: You may want to copy and paste formulas from Player 1, or use a "Fill" command for copying and pasting the formula).
   7. Enter formulas that calculate the team's total score for all games in cell D6 and the teams overall average in cell E6.
   8. Change some of the players' scores. Once you are convinced that the totals and averages are being calculated correctly, use the Save as... command from the File menu to save the spreadsheet in its current form. A dialog box will appear on the screen that will allow you to name the file and to decide which disk it should be stored on. After saving the spreadsheet, clear the ledger by selecting New from the File menu.

2. Use your spreadsheet to define a ledger that acts as a grade book for a class of 10 students. Each student is to complete two homework assignments (each receiving a numeric grade out of 25 points), one midterm exam (50 points), and one final exam (100 points). The spreadsheet should calculate the numeric class average for each assignment and exam. Each student's weighted average is also to be calculated, given that the homework constitutes 50 percent of the grade, the midterm 15 percent, and the final exam 35 percent.

3. Finally, use your spreadsheet program to create a bar graph displaying the weighted averages for each of the 10 students in the class.