Using Excel: Grade Calculator

file: d:\B173-2015\grade_calculator

date: August 31, 2015

Introduction

To give you some practical experience using Microsoft Excel, you are to complete the following assignment: construct a grade calculator for this (or any) course.

To do this, you will use the basic skills you developed in the last assignment. I have included a sample of what such a calculator might look like.

Procedure

We will assume that for this grade calculator, you have the following specifications. Your final grade consists of two parts, a lecture part worth 2/3rds and a lab part worth 1/3.

We will assume that your lecture grade is calculated from the following four items:

Item	Percentage Possible

Midterm 1	20

Midterm 2	25

Final Exam	35

Term Paper	20

---

Total = 100%

Your lab grade is similarly calculated:

Item	Percentage Possible

Midterm	25

Final	25

Labs	50

---

Total = 100%

Technical Issues

Most of the calculator is straight-forward but here are a few thoughts and suggestions.

Exams may be out of an arbitrary number of points and that number does not influence the weight of the item in the overall grade calculation. In other words, Midterm 1 might have only one question and be graded out of 16 or it might have 100 questions and be graded out of 1000. Regardless, it is still worth 20 percent.

So your calculator needs to convert your score to a percent obtained.

Multiplying that percentage by the percent possible (column B) will yield the weighted value of your score. The sum of all those weighted values will be your total for the lecture portion of the course.

[Think about why this works. If you have trouble with this, try "extreme values", i.e., imagine that your score on the exam was 0, what would your percent obtained be [0], and then what would its weighted value be? [0]. But, what if you aced the exam and got 70 out of 70, then what would your percent obtained be? [100] The weighted value would be 20, i.e., you would have received the maximum available in that category]

For those who have difficulty with "seeing" mathematics, the idea of using extreme values is a great way to explore the behavior of a mathematical calculation. This will be useful later in the course.

How to deal with the fact that your lecture grade is worth twice that of the lab grade? One way to do this is to multiply the lecture grade by 2, multiply the lab grade by 1, add them together and divide by 3. This has the effect of weighting the lecture twice the value of the lab.

Nice touches

You can change the width of a column by manipulating the column title. Expand or shrink it by using your mouse on the divider line between column titles.

You can format cells including the background color. Do not overdo this but judicious use of cell formatting can improve the readability of your work.

Be sure to display only a reasonable number of decimal points, e.g., 2 decimals. You can change this by formatting cells.

You can print the column and row titles (i.e., the A, B, C along the top) by hitting Print Preview and then Setup and Sheet. This is handy when trying to explain to someone where something is on a complex spreadsheet, for example, "if you look at cell C19, you see...."

Assignment:

Produce a grade calculator spreadsheet and hand it in. You may assign yourself whatever grades you would like :) Be sure that your spreadsheet looks a lot like mine in overall appearance, i.e., a similar number of decimal places.

-- END