The objective of this project is for you to hone your proficiency in utilizing a spreadsheet for numerical calculations. You are to create your own spreadsheet to solve the grade calculation problem described in the following paragraphs.
Part 1: Solution to the Basic Problem
We wish to create a spreadsheet which will automate the computation of grades for a class. The work to be graded in the class in question consists of two tests, two lab projects, and a final exam. Each test will count 25% of the grade; each lab project will count 10%; the final exam will count 30%. However, the instructor varies these percentages occasionally, so we'd like to construct our spreadsheet so that these values are problem parameters (as opposed to being hard-coded into formulas) which can be easily modified without changing any formulas directly. Begin your project by constructing a spreadsheet that will compute the weighted average for each student in the class. Of course, the spreadsheet should be constructed so that all results are automatically recalculated whenever a grade is changed. The final display should look something like the following (use the data given here when you test your spreadsheet):
Tests =
25%
Projects =
10%
Exam =
30%
Name Test 1 Test 2 Project 1 Project 2 Exam Average Bill Jones 80 70 90 85 83 79.90 Nan Knox 75 65 80 90 88 78.40 Jill Smith 83 99 87 89 90 90.10 JoeTimms 77 88 92 76 82 82.65
Try changing several of the grades in one of the rows. Check the revised spreadsheet calculation by computing the desired average using your calculator (or by hand) and comparing. If there is a discrepancy, find the error and correct the spreadsheet if necessary. Change the grades back to their original values before proceeding.
Next, try changing the percentage weights so that tests are now weighted 20% each, lab projects 10% and the final exam 40%. Check several of the new calculations your spreadsheet makes by computing the results with your calculator (or by hand) and comparing. If there is a discrepancy, find the error. If the spreadsheet results are incorrect, identify the problem and correct it. Before proceeding, change the percentage weights back to their original values.
Part 2: Adding Some Statistical Summaries
Suppose the instructor finds it convenient to have the average and standard deviation computed for all the grades (tests, projects, exam, and final averages). Use the built-in Excel functions AVERAGE and STDEV to compute these values and add them to the spreadsheet as shown below:
Tests =
25%
Projects =
10%
Exam =
30%
Name Test 1 Test 2 Project 1 Project 2 Exam Average Bill Jones 80 70 90 85 83 79.90 Nan Knox 75 65 80 90 88 78.40 Jill Smith 83 99 87 89 90 90.10 Joe Timms 77 88 92 76 82 82.65 Average 78.75 80.50 87.25 85.00 85.75 82.76 St. Dev. 3.50 15.80 5.25 6.38 3.86 5.20
Part 3: Computing Letter Grades
The instructor uses a standard 10-point grading scale, but again may choose to change this scale later. Enhance your spreadsheet to include the calculation of the letter grades using the Excel IF function. Include the grading scale limits as problem parameters (similar to the way we did for the weighting percentages earlier), so these limits can be easily changed. Of course, any changes in numerical grades within the spreadsheet should result in automatic recalculation of the grades assigned. And a change in the grade scale limits should be automatically accounted for in the computation of the letter grades. Your modified spreadsheet should look something like the following:
Tests =
25%
A scale is
90 through 100
Projects =
10%
B scale is
80 up to 90
Exam =
30%
C scale is
70 up to 80
D scale is
60 up to 70
F scale is
0 up to 60
Name Test 1 Test 2 Project 1 Project 2 Exam Average Grade Bill Jones 80 70 90 85 83 79.90 C Nan Knox 75 65 80 90 88 78.40 C Jill Smith 83 99 87 89 90 90.10 A Joe Timms 77 88 92 76 82 82.65 B Average 78.75 80.50 87.25 85.00 85.75 82.76 St. Dev. 3.50 15.80 5.25 6.38 3.86 5.20
Try changing several of the grade scale limits, so that at least one of the letter grades will change. Check the revised spreadsheet calculation of the letter grades. If there is a discrepancy, find and correct the error. Change the grade scale limits back to their original values before proceeding.
Part 4: Adding a Grade Distribution and Histogram
Add an automatic computation of the grade distribution (by letter grade) to your spreadsheet using the Excel COUNTIF function. Once this distribution is calculated, construct a histogram (bar chart) to display the distribution graphically. Display this chart on the sheet (not as a separate sheet). For the above spreadsheet this would mean adding the following components:
Grade Number A 1 B 1 C 2 D 0 F 0
Change some of the numerical test and/or lab project grades in the original data (make the changes drastic enough to be certain some letter grades will change). Observe the corresponding changes in your chart. If these are not consistent with the changes in the letter grades assigned, find the error and correct it. Change the data back to their original values to conclude the project.