Practicing Excel by having students calculate their grade

One way to engage students is to have them work on problems that they can relate to. An obvious example is to let them work out their course grade, which is often a weighted average of their scores on various components. An example is listed below:

Course requirement Weight Max pts. Points
News presentation 5% 5 5
Simulations 10% 10 8
In-class essays 15% 40 30
Accepi assignments
– Assignment 1
– Assignment 2
– Assignment 3
20% 100
130
240
80
115
190
Final exam 20% 110 100
Clickers 30% 50 46

I provide this table to my students and have them form groups of two or three and work on the following questions using Excel or Google Sheets:

  1. What’s your final percentage?
    Hint: Try SUMPRODUCT()
  2. Now assume that you haven’t got the final score yet. What’s your percentage so far?
  3. What’s your percentage so far if you can drop the assignment with the lowest percentage score (and don’t have the final score yet)?

FYI, the answers are 86.4%, 85.3% and 86.0%. When missing the final score, the trick is to replace the weight of 20% for the final by zero and scale up the total result by 1.25, since the final is worth 20% and dividing by (1-20%) = 0.8 (or multiplying by 1.25) extends the maximum total score from 80% back to 100%.

Advertisements

One thought on “Practicing Excel by having students calculate their grade

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s