Embedded Files
Grade Calculator:
Oh wait! We’re not even close to done! What’s left to do?
1. First of all, the clicker questions are portrayed as numbers answered correctly, but it’s supposed to be that you get just 1 participation point for each clicker session – IF you get at least ½ of the questions correct. Right now we just have the number of questions correct in the cell.
2. Second of all, the Final exam score cell is blank, and therefore a 0 is calculated into the total grade!
3. Finally, this class (as with most classes) has weighted percentages for each topic! Let’s say the labs are worth 30%, the midterm is worth 20%, the final is worth 40% and clicker questions are worth 10% (note: this may not be the actual weighting in the class you are currently taking – this is just an example).
Wow, that seems like a lot of issues to face! But if we break it down step by step, it should be easy!
First of all, we need to fix the clicker data to make sure the points are computed correctly. As a reminder, we want to assign either 0 or 1 points per clicker session – where we assign one point if the student got at least half of the clicker questions correct. The current data in the clicker columns is the number of questions that the student got correct that session.
We need to add 3 columns to the left of the Clicker 1. To do so, we can right click on B and then press on the Insert Column Left button. Lets do that 3 times. Then let’s add the name Clicker1P, Clicker2P, Clicker3P
Oh no! The columns are too big now and we need to scroll. Later on we’ll teach you how to use the freeze frame feature to help with this, but for now, lets just move the mouse cursor to the top of the cell next to the letters. At the end of each cell the mouse pointer will turn into an arrow. Click on it and hold and drag to resize.
Now let’s change the total max (in row 8) for Clicker1P, Clicker2P, Clicker3P to 1 and Clicker1 to 12, Clicker 2 to 7, and Clicker 3 to 10. Then delete the row underneath the total max with those temporary clicker numbers . Your spreadsheet should look like this right now.
Now let’s calculate the Clicker Participation points correctly. Similar to if statements in Alice, you can use if statements in spreadsheets as well! There is a built in function that does if for you. The syntax (structure) is
=if(condition,valueiftrue,valueiffalse).
Ifs in spreadsheets are generally simpler than ifs in Alice – in that what we “do” in the true or else portion is often just assigning a value to this cell.
In the above:
· Condition is a Boolean expression that evaluates to true or false.
· Valueiftrue is a value (could be number or text) that you want to assign to the cell if the condition evaluates to true
· Valueiffalseis a value (could be number or text) that you want to assign to the cell if the condition evaluates to false
Although we can’t really do this in Alice, it would kind of look like
Let’s get back to our real world example.
In clicker questions, we need to see if the number of correctly answered questions is more than half of the total questions asked (recorded in Total Max) to give full points.
We can write out the statement as
If (Clicker >= (1/2 * Total Max))
1
Else
0
Let’s translate that into the Excel function. Since the syntax is =if(condition,valueiftrue,valueiffalse) we can just type =IF(E2 >= (0.5 * E8), 1, 0) and it will evaluate to 1 or 0!
Page updated
Report abuse