Assignment 8: ExcelGradebook

In this activity you will create an Excel spreadsheet to calculate students’ grades. There will be 3quiz grades and an exam grade. The exam counts 40% of the final grade and the quizzes 60%. The spreadsheet will calculate the weighted final grade as a percentage, calculate the equivalent letter grade, and rank the students.

1. StartProgramsMicrosoft Excel. You should see a new blank worksheet.

2.In row 1 enter the headings as shown below. In rows 2 – 6 enter data for five students. You can use any names and any gradesas long as no grade exceeds 100.

A / B / C / D / E / F / G / H / I
1 / ID No. / Name / Quiz1 / Quiz2 / Quiz3 / Quiz avg / Exam / Final grade / Letter Grade
2 / 1010 / Mary / 70 / 89 / 75 / 77
3 / 1011 / Juan / 88 / 77 / 85 / 64
4 / 1012 / David / 69 / 82 / 80 / 82
5 / 1013 / Sarah / 100 / 70 / 70 / 39
6 / 1014 / Felicity / 95 / 90 / 65 / 71

3. To calculate the average of the three quizzes:

a. Move to cell F2. From the Insert menu, choose Function. Choose Average. Click OK.

b. Verify that the range of cells to be averaged is correct (C2:E2) and click OK.

4. To replicate the Average function for the other students:

a. Drag to highlight cells F2 through F6 and choose Edit  Fill Down.

5. Now enter a formula for the final grade, which is weighted 60% quizzes and 40% exam.

a. Move to cell H2 and click in the formula bar. Type: =0.6*F2+0.4*G2and Enter.

b. Drag to highlight cells H2 through H6 and choose Edit  Fill  Down.

c. Format the cells to one decimal place (FormatCellsNumberNumber)

d. Look at the resulting grades to verify that they make sense. If not, you probably did something wrong.

6. Save what you’ve done so far (FileSave As).

7. Suppose you want to rank the students.

a. Place the cursor on the far left side so that it becomes an arrow and drag it down to highlight all the rows.

b. From the Data menu, choose Sort.

c. Choose Sort by Final grade, descending, with header row. Click OK.

8. Now put the student names in alphabetical order.

a. Place the cursor on the far left side and drag it down to highlight all the rows.

b. From the Data menu, choose Sort.

c. Choose Sort by Name, ascending, with header row. Click OK.

9. To calculate the letter grade, use a formula involving If…Then…Else statements.

a. Move to cell I2.

b. Click in the formula bar and type: =IF(H2>=90, "A", IF(H2>=80, "B", IF(H2>=70, "C", IF(H2>=60, "D", IF(H2<60, "F")))))and press Enter.

c. Drag to highlight cells I2 through I6 and choose Edit  Fill  Down.

10. Let’s add another column to rank the students based on their final grade.

a. In cell J1, type Rank.

b. Move to cell J2. Click in the formula bar and type: =RANK(H2,H2:H6). This calculates the rank of H2 (the first student’s final grade) in the range H2:H6 (all students). Press Enter.

c. Drag to highlight cells J2 through J6 and choose Edit  Fill  Down.

d. Do the resulting ranks make sense? No, something is wrong! What? Examine the formula for J3. The range in the formula should have stayed as an absolute range J2:J6 but it changed relative to the row. That’s what normally happens when you use Fill.

e. Let’s fix the problem. Excel uses the $ symbol to indicate an absolute reference to a cell or a range of cells. Change the formula in cell J2 to: =RANK(H2,$H$2:$H$6). Then drag to highlight cells J2 through J6 and choose EditFillDown.

11. Save your work again (FileSave).

12.Problem solving challenge:

Go to a blank area of the worksheet and create a frequency table that calculates how many students received each letter grade. Hint: use the COUNTIF function to count how many times “A” occurs in the range I2:I6; for example, =COUNTIF(I2:I6, “A”).Similarly, for “B”, etc. If you FillDown, remember to make the range absolute rather than relative.

Add a column to show the percentage to one decimal place (FormatCellsNumberPercentage and set Decimals to 1).

Grade / Freq / Percent
A / 1 / 20.0%
B / 2 / 40.0%
C / 1 / 20.0%
D / 0 / 0.0%
F / 1 / 20.0%
Total / 5 / 100.0%

Then make a pie chart of the results.The chart should have a title and a legend. (Obviously, your data will be different from mine.)

Check yourself:If you change one student grade,both the frequency table and the pie chart will update automatically. Try it!That’s the power of spreadsheets.

13. Save again with the chart and frequency table visible. Use your last name and 8 as the file name, e.g. smith8.xls. Submit to the Digital Dropbox.