Activity: Creating an Electronic Grade Book
Scenario: You have all of your students' quiz averages recorded in your grade book. You wish to use Excel to quickly calculate everyone's averages.
Step 1
Enter the following data into Excel:
| Name | 5/1 | 5/8 | 5/15 | 5/22 | 5/29 |
Average
|
Letter Grade
|
| George | 58 | 76 | 67 | 75 | 78 | ||
| Sarah | 98 | 90 | 89 | 95 | 87 | ||
| Telly | 87 | 83 | 77 | 80 | 85 | ||
| Elmo | 77 | 80 | 85 | 82 | 85 |
Step 2
Calculate George's average:
- Click on the cell where you wish to input George's average.
- In Excel, click on the function icon
- Select Statistical in the Function Category and AVERAGE in Function Name.
- Click OK.
- A box will appear asking which cells you wish to average. Simply highlight the cells from "58" to "78" in George's row.
- Click OK in the box that had appeared.
Step 3
Calculate everyone else's averages by copying formulas:
- Select the cell where you calculated George's average.
- Click and drag the fill handle at the lower-right corner of the cell to the bottom of the column.
- Notice that everyone else's average has now been calculated!
Step 4
Creating letter grades from numerical scores:
- Click on the cell where you wish to input George's letter grade.
- Enter the the following line very carefully:
=IF(L3>89,"A",IF(L3>79,"B",IF(L3>69,"C",IF(L3>59,"D","F"))))
- Click and drag the the fill handle from George's letter grade all the way down to Elmo's letter grade.
Challenge:
Calculate the class mean, median, and standard deviation for each date.