Homework 9 – Fractals



Assignment 6 – Spreadsheets I

Dean Zeller Due Thursday, October 23rd

CS10001 Lab: 5 points

Fall, 2008 Assignment: 10 points

Objective The student will use a spreadsheet to create a teacher’s gradebook, including data, labels, and weighted percentage calculations.

Background

Spreadsheets are second only to word processors for use in the industry. Any application that requires numbers and calculations can be completed using a spreadsheet such as Microsoft Excel. This assignment is a version of the actual gradebook used to calculate grade for my classes.

Lab – Textbook practices, chapters 7 and 8 (partial)

The lab component for this assignment consists of completing the practices from chapters 7 and 8 in the textbook. Unlike the word processing chapters, not all of the practices are required. The practices that include material needed to complete the assignments are required. The practices not required are still useful knowledge and may still be completed for 1 point of extra credit.

Start page Topic Required?

Ch7, p210 Entering data Yes

Ch7, p214 Formatting Yes

Ch7, p218 Formulas with numbers Yes

Ch7, p221 Formulas with cells Yes

Ch7, p224 Copy to Word Yes

Ch7, p226 Collaboration Skip

Ch7, p229 Graphics and hyperlinks Extra Credit

Ch7, p231 Conditional formatting Extra Credit

Ch7, p233 Templates Extra Credit

Ch7, p236 HTML file Extra Credit

Ch8, p260 What If questions Yes

Ch8, p262 Absolute cell reference Yes

Ch8, p265 Min, Max, and Average Yes

Report – Gradebook

For the report, you will create a teacher’s gradebook, similar to the one your instructor uses every semester to keep track of student assessment. Follow the template to create your own gradebook with the requirements specified below.

Turning in your assignment

You must print two copies of your spreadsheet. Print one copy with the cell values, and the other with the cell formulas. Use the control-backquote (ctrl `) key to toggle between the two views. (The backquote is directly above the tab on the keyboard.) Switching between views messes with the set column widths, so resize the columns to show the entire text within the cells.

Grading

You will be graded on the following criteria:

Effort Completing the gradebook data, labels, formatting, and formulas

Organization Readability and correctness in formatting

Extra credit will be given for the following:

• Create an additional spreadsheet relating to your career interests using the material covered in the practices.

Gradebook Requirements

• At least 20 students. Column A contains the student name, and data starts in column E. Enter names of people you know (friends, family members, famous people).

• At least 10 course requirements (assignment, exam, quiz, other). Number each requirement by type, as shown in the example.

• Column label formatting (row 1): bold, center alignment, underline

• For each student, the points cell in column D calculates the total number of points earned by the student by summing the grades for all of the requirements completed using the =SUM function.

• Summary label formatting (bottom 4 cells in column A): italics, right alignment

• Requirement value (row 11 in example) contains the weighted value of the course requirements (assignments: 10, exams: 50, quizzes: 5, and other course requirements as you see fit)

• Requirement average (row 12 in example) is a calculation (using the =AVERAGE function) of the average grade achieved by all students. The average should be displayed as a number with one digit after the decimal place.

• Requirement minimum (row 13 in example) is a calculation (using the =MIN function) of the lowest grade achieved by students for that requirement.

• Requirement maximum (row 14 in example) is a calculation (using the =MAX function) of the highest grade achieved by students for that requirement.

• The percent (column C) is a formula that calculates the student’s weighted average by dividing the number of points earned by the total number of points possible for all requirements. This uses the absolute cell reference of the value total (cell D11 in the example). The percentage should be displayed with one digit after the decimal place.

• For the Grade (column B), leave blank. In the next assignment, you will create a formula to calculate the letter grade earned by the student based on the percentage.

Cell Value View

Use Control-backquote (ctrl `) to toggle between Cell Value and Cell Formula views.

|A |B |C |D |E |F |G |H |I | |1 |Name |Grade |Percent |Points |Assign 1 |Quiz 1 |Assign 2 |Assign 3 |Exam1 | |2 |Blascovik, Joe | |94.1% |80 |10 |5 |8 |10 |47 | |3 |Edwards, Steve | |81.2% |69 |8 |4 |9 |9 |39 | |4 |Tarkington, Frank | |97.6% |83 |10 |5 |9 |10 |49 | |5 |Squire, Jennifer | |89.4% |76 |9 |5 |8 |10 |44 | |6 |Croninger, Bob | |8.2% |7 |5 |2 |0 |0 |0 | |7 |Irgiry, Luce | |76.5% |65 |6 |3 |6 |10 |40 | |8 |Ali, Walter | |94.1% |80 |10 |5 |8 |7 |50 | |9 |Smith, Olga | |67.1% |57 |7 |5 |7 |8 |30 | |10 |James, Nancy | |85.9% |73 |8 |5 |9 |7 |44 | |11 |Value |  |  |85 |10 |5 |10 |10 |50 | |12 |Average | |77.1% |65.6 |8.1 |4.3 |7.1 |7.9 |38.1 | |13 |Minimum | |8.2% |7 |5 |2 |0 |0 |0 | |14 |Maximum |  |97.6% |83 |10 |5 |9 |10 |50 | |

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download