Review the Excel resources in the Week 5 Resources module and complete the simple Excel Tutorial prior to completing the assignment.  The Class Gradebook assignment must be completed using Microsoft Excel or equivalent spreadsheet software.  Save this ass

Mrs. Reeshemah Johnson

EDUC 210

Excel Assignment – Create a Grade Book (25 points)

1. Create a grade book spreadsheet. I have included an example of what your grade book should look like on the next page. Do not type Student 1, Student 2, etc. Create your own student names.

a. You should have 15 students. (2 points)

b. Each student should complete (4 points)

1. four assignments

a. enter grades for each of the four assignments

b. calculate the average of the four assignments (must be a formula) in a separate column

2. two tests

a. enter grades for each of the two tests

b. calculate the average of the two tests (must be a formula) in a separate column

3. a final exam

All grades should be out of 100.

c. Use formulas to calculate the following: (7 points)

1. The class average for each assessment

2. Each student’s final numeric grade. In column L, add the following:

a. 30% (.30) of the assignment average

b. 45% (.45) of the test average

c. 25% (.25) of the final exam grade

3. Each student’s letter grade based on a 10-point scale, A, B, C, D, F (you should have at least one student in each category). You can use the formula from the tutorial. Just copy and paste it. Once you paste the formula, you will have to:

a. Remove all the decimals (eg. L5>92)

b. Make sure the formula is using the right cells.

4. The average final grade

d. Indicate the following (you must use a formula): (5 points)

1. The highest final grade (max)

2. The lowest final grade (min)

3. Use conditional formatting to indicate the students with the highest and lowest grades

a. Highlight the final grade range

b. On the ribbon, click Conditional Formatting

c. Click Highlight Cell Rules

d. Click Equal To

e. Enter the cell that contains the highest grade and set the formatting to green fill with dark green text

f. Repeat a thru d above, enter the cell that contains the lowest grade and set the formatting to light red fill with dark red text.

e. Formatting (2 points)

1. Center align the range C4:M19

2. Bold rows 1, 2, 4 and 21, 22, 23

3. Place a border around the range A4:M23

4. Adjust the column rows so that all your headings show

5. Sort the range A5:M19 in ascending order by last name

2. Create a column graph showing how the students performed overall (based on final grades). (5 points)

a. Include the students’ last names on the graph.

b. Use the Chart Tools menu to:

1. Change the chart style to a black background with red bars.

2. Change the chart type to a 3-D clustered column. Change the Chart Layout to Layout 5.

c. Remove the Y-axis title (it’s the box on the right of the graph – click it and click delete).

d. Change the chart title to Final Grade Distribution.

3. Save the document as StudentGradesYourInitials (for example, StudentGradesRJ) and submit.

In my example below, my formula for Student 1 would be:

=(G5*.30)+(J5*.45)+(K5*.25)

Click on the Home tab, and click on the down arrow beside Autosum (all the way to the right on the ribbon) to access these formulas (max & min).