5. Averaging

Since the overall course grade is weighted, we need to calculate an average of all the clicker questions and an average of all the labs. Because this is so common, spreadsheet programs (like Excel and Google spreadsheet) have a function that does this calculation formula for you! We need to add two more columns to store our averages, so right click column E and press insert column left. Repeat with column K. Then label them ClickerAvg and LabAvg respectively.

Look at Clicker1P. Before we added those columns the formula referenced E2 and E8 – but now it says F2 and F8! Spreadsheets are smart enough to update all your columns and cells for you when you insert (or delete) a column (or row)! Also, let's delete the column with the erroneous grade calculation. Your sheet should look something like this right now.

Let’s get the average right now. As one might guess, the syntax for average function is just =AVERAGE(cellnumber1,cellnumber2,…numbern) with as many parameters as it wants (which is the same as one can do with SUM). For the ClickerAvg let’s make an intentional mistake -- put =AVERAGE(A2,B2,C2).

Wait! A2 isn’t a number how do you average a string of text? Well press enter, and you’ll see 0.5. Most spreadsheets are smart enough to ignore any cells that aren’t numbers and don’t add them into your calculation (by the way, this has only become common in the past few years). However, we do need the correct cells, so lets change that to =AVERAGE(B2:D2) [or =AVERAGE(B2,C2,D2) – these are the same thing].

Look! It calculated that you have only .33333 or 33.33% in your clicker question! If you had to do it by your own formula, it would have been =(B2+C2+D2)/3. Now imagine of you need to work with 1000s of cells of data! It’s so much easier to just let the computer do things for you (and be sure to use the range (e.g. B2:D2) syntax in that case)!

[Q13.5.1] How would you fill out the Total Max for ClickerAvg? Fill that out and also fill out L2 and L8 as well using the same for LabAvg. We’ll be using it later.

Your excel sheet should look like this right now.

<< Back Next >>