CSCI-A110 Lab 7: Spreadsheets I

• 50 points
• Assigned: 2011 March 17
• Due: 2011 April 5 at 4:30 p.m.
• URL: http://mypage.iu.edu/~gdweber/csci/a110/lab/07-spread1.html

This is version 3.4

Lab Objectives

1. Understand the basic layout and purpose of a spreadsheet.
2. Lay out a spreadsheet application and format the spreadsheet for readability and appearance.
3. Enter data onto a spreadsheet and edit cell contents.
4. Understand the importance of letting formulas do the work for us in spreadsheets.
5. Create and apply appropriate formulas to meet the goals of a spreadsheet application.

Instructions

Recommended spreadsheet programs for this assignment include Gnumeric, Openoffice.org Calc, and Microsoft Office Excel. You may use another spreadsheet, provided that it is able to save files in an acceptable format.

Imagine you are a teacher and create a grade book spreadsheet.

Enter Data

1. At the top of the spreadsheet, insert headings that identify the class (A110), your name, and the current semester. (You may make up the class and semester, but not your name.)
2. Below this, insert column headings for students' last and first names, paper 1-4, exam 1-2, average paper, average exam, total points, and course grade. See the example below, Table 1.

3. Below the column headings, insert 5 rows of data for five students.

   Use the exact data shown in Table 1 for last and first name, paper 1-4, and exam 1-2. Do not make up student names or scores. Do not insert any numbers in the average or total points columns, and do not enter any grades in the course grade column.

Set Up Formulas and Formats

4. Enter the formulas and set the format for the first row as follows:

   1. In the column for average paper, in the row for the first student, insert a formula which adds the student's four paper scores and divides the sum by 200 (each paper is worth 50 points). Remember to begin a formula with the = sign. You can use the sum function or + to add, / for division, and parentheses () for function arguments or for grouping.

      Do not calculate the average manually or enter a number in this cell! You must use a formula to let the spreadsheet do the calculation for you.

      If you enter a correct formula, it will evaluate to 0.975 in the row for the first student.

   2. Likewise, enter a formula for the average exam column in the row for the first student. This is similar to the average paper formula, and the divisor is again 200. If you get this formula correct, it will display 0.79.
   3. Select the cells of the two formulas, and use the spreadsheet's "Format cells" command to display the numbers as percentages with one digit to the right of the decimal point. The numbers displayed should change to 97.5% and 79.0%.
   4. Still in the first student's row, enter a formula for total points. This formula should add the four paper scores and the two exam scores. If you get this formula correct, the value shown is 353.

   5. In the course grade column, enter a formula for the first student's grade using the if function. The course is graded pass or fail, and the passing score is 240 points (60% of 400 points). Hint: The if function works like this. Suppose you wanted to know if a temperature in cell B4 is or is not at least 100 degrees. The formula =if(B4>=100,"yes","no") will display yes if the value in B4 is at least 100, and no otherwise. Adapt this example to the gradebook problem, to show the grade as P for passing or F for failure. You must use quotation marks around P and F, because they are character strings.

5. Now, check that everything in the first student's row is correct. Okay? If everything looks fine, then select the four cells with formulas that you just entered and use the spreadsheet program's Copy and Paste commands to copy the formulas into the remaining student rows. Note that this copies the formatting as well, so if you ever change either the formula or the formatting, you will need to copy the new version to all the rows.

Final Steps

6. Check your work by manually calculating the proper averages, total points, and course grade for a student who has less than 240 points and therefore fails. Also, make sure that all numbers in the two "average" columns are shown as percentages.

7. Save the spreadsheet. You will be turning it in electronically, and it must be saved in a file format that the instructor can open. See acceptable file types above.

8. Login to Oncourse (https://oncourse.iu.edu/), go to A110, and select the "Assignments" tool. Select "Lab 7." Scroll down to the "Submission" section. Click on "Add Attachments," upload your spreadsheet file, and then click on "Submit". Note: As stated in the syllabus, you will be able to turn in this assignment up to two class weeks late (not counting spring break) for partial credit.

Turning It in

Checklist

Before turning it in, make sure that:

• You have used formulas in all cells where formulas are required.
• You have saved the file in an acceptable file format.

What to Turn in

Just your spreadsheet file, electronically submitted through the Oncourse Assignments 2 tool. There is no paper to turn in for this assignment, not even a URL.

Grading Criteria

The file must be saved in an acceptable file format and uploaded into Oncourse Assignments, or it cannot be graded.

Revisions

• Version 3.4, revised 2011 Mar 16, revised due date.
• Version 3.3, revised 201 Oct 28, corrected instructions for turning in the assignment.
• Version 3.2, revised 2010 Oct 26, converted to markdown, revised dates.