Embedded Files
Continuing on, let's add some more data.
Let's say we know the total for each lab is 15 points, so write 15 in cells E7, F7, G7. We know that the midterm is worth 26 points and let’s assume the final is worth 52 points. Let’s also add clicker participation points, which are worth 1 point. Fill in the data and your spreadsheet should be something similar to this.
Now let's populate the chart with some "fake" grades. On the first lab you did very well and got full credit, but on the second lab you forgot something and lost 2 points. You got a 25 on your midterm as well. On the third lab, you didn’t finish, and only got 7 points. Now let’s compute the clicker questions.
On the first clicker question set you were correct about 8 questions out of 12. You only got 3 out of 7 questions correct on the 2nd clicker set, and you missed all 10 questions on the third clicker set because you were too busy taking care of your grandma. So let’s input the numbers for each score (clickers, labs, midterm, and final) in Row 2. Of course since you haven’t taken the final yet, you leave it blank.
Alright, now that we’re done setting up our current spreadsheet, let’s make this into a grading calculator.
First of all, just for practice, lets calculate a sum of all the scores. Just as expected of programs, there are multiple ways of doing so. But we need to run a formula first, so let's click on J2, and type = to start the formula.
Let’s use the SUM formula. SUM takes in as many parameters as we want, with commas in between, and adds up everything as those parameters. So we can do SUM(B2,C2,D2,E2,F2,G2,H2,I2) as the 8 different parameters to sum up.
(SUM and all the functions introduced here are not only Google spreadsheet functions, but also Excel functions – and should be functions in any modern spreadsheet program).
We can also write =SUM(B2:I2) and it will add everything in between!
In the example above, our SUM function has two parameters – but they are kind of funny – there is a : in between them. This is the way spreadsheets indicate a range of values, so =SUM(C1,C2…CN) takes in as many parameters as it wants, with each cell or group of cells separated by a comma. For example you can have a group of cells as one parameter, like SUM(B2:I2), but also something with a mix, such as SUM(A1:C1, F1:I1), where it adds the sum of A1 through C1 to F1 through I1.
Now we have to calculate a total for the total max row so let’s type =SUM(B7:I7) in cell J7. Now under that in cell J8, we have to calculate the percentage of our grade. Let’s just put in the formula J2 divide by J7 and then we’ll be done right?
[Q13.2.1]: How would we add just the clicker questions and midterm? *hint* You can sum multiple cells with commas inside the SUM function.
· Tip * If you want to just use the equal sign as an equal sign and not as a way to start the formula, put an apostrophe (‘) in front of the equal sign. That is if you want a cell to have the text = is a very cool character you would type in the cell ‘= is a very cool character
Page updated
Report abuse