6. Finish Grade Calculator

We are almost done with creating a grading calculator! Now we need to calculate the overall course grade – but weighting the scores in each category by the appropriate percentage (as defined by the professor of the course – we listed that at the beginning of this module).

Next to the Final, let’s create a column called weighted total. Then, let’s do some slightly more complex math! It is (ClickerAvg/TotalMaxClickerAvg * 10%) + (LabAvg/TotalMaxLabAvg * 30%) + (Midterm/TotalMaxMidterm * 20%) + (Final/TotalMaxFinal * 40%). This would be so crazy if we had to work it all out by hand and to keep track (especially for hundreds of students)! But thankfully the spreadsheet will do this for us and will update anything accordingly.

In our formula we will be referencing TotalMax with an Absolute Cell Address (as they should always stay the same) but we will use Relative addresses for the other variables/cells as then we can reuse this formula later on (e.g. on different rows of students).

To calculate what you have in the class type =((E2/$E$8) * 0.1) + ((L2/$L$8) * 0.3) + ((M2/$M$8) * 0.2) + ((N2/$N$8) * 0.4).

But why is the score so low? Well unlike average, doing this formula doesn’t ignore the empty cells. (We’ll assume empty cells means incomplete, not 0, and therefore should be calculated in the grade). Yikes – you mean we’ll have to visually inspect our entire spreadsheet to check if there are empty cells? That could get time-consuming and the more data we have, the more likely we’ll overlook something.

It would be great if somehow we could get the spreadsheet to do this work for us…

There are functions in spreadsheet programs called COUNT, COUNTA, and COUNTIF. These can be used for a variety of things – but we’ll work towards using them to see if a cell is empty. We will discuss COUNTA and COUNTIF in a short bit but for now, let’s see how COUNT works.

The COUNT function takes one or more parameters (which cells to count) and returns how many cells are there that have numbers in them. In any blank cell, such as cell Q2 , let’s test it out by typing =COUNT(B2:N2). It should return 12, even though there are 13 cells in the range B2 to N2 (count the columns with your fingers to convince yourself). This is a great way to check if a cell is empty or not as it will return 0 if the cell has nothing in it. Just like AVERAGE, it will ignore (not count) cells with text too. The COUNT function only counts cells with numbers.

So let’s figure out a formula that checks to see if the midterm score cell or the final score cell is blank. But even if they are – we still want to calculate a “reasonable” score – not imagine that you have a zero on those exams.

Since we know that the final is worth 40%, we need to just divide the current score by 0.6 to calculate a score “without” the final exam. We also know that the midterm is worth 20%, and thus if the midterm was missing as well then we need to divide the current score by 0.4. (1-(20% + 40%)). Note: it doesn’t seem likely you would have a missing midterm, but have a final – since you take the midterm before you take the final.

In Alice, this is how we would generate the statement:

If (Final is missing)

If (Midterm is missing)

CurrentTotal / 0.4

Else

CurrentTotal / 0.6

Else

CurrentTotal

Now let’s translate that into spreadsheet calculations.

First let’s enter the name Current Total in cell P1.

Then, we need to figure out how to check to see if the final score is missing. Lucky for us, spreadsheets have functions that perform like if statements do in Alice.

IMPORTANT NOTE: IFs in spreadsheets do not do all the things if statements can do in Alice. In Alice we could put a lot of method calls and other things in an if statement. In spreadsheets we are always talking about filling a cell with a value. So in Alice, the IF is a function – something that returns or evaluates to a value. In this example the value returned (remember that vocabulary from Alice functions?) is a number. But it could be text as well.

The function we will use is (more or less)

=IF(COUNT($N2)=0,dosomething,dosomethingelse)

Note: We’ll be keeping the column absolute to N as it will always be the Final column.

Note: In spreadsheets, instead of == to check for equals (which is what we used in Alice and is used in many programming languages), it is only a single = sign. If you use ==, excel will give error as it doesn’t understand the equation.

Once we have the outer If statement (function in this case), we need to make an inner if statement to check if the midterm is missing or not. The final statement is computed as (we will use column O as absolute as we want to always use that column for Weighted Total)

=IF(COUNT($N2)=0, IF(COUNT($M2)=0, $O2/0.4, $O2/0.6),$O2)

[Q13.6.2] How would you make it so that it doesn’t have to check if the final score exists to check if the midterm score exists? * hint * the total number to divide will be 0.8 if the final exists but not the midterm. We know it seems odd, but it’s a great way to check if you understand what we’ve been doing!

So we just made a grade calculator! That wasn’t so complicated right? OK, maybe it did take some time. But really, there wasn’t anything there that you can’t at least imagine connecting to things you learned in Alice – functions, parameters, if statements. OK – the relative and absolute addressing is new. And if you are a bit confused about it – many other people are too. Next, we’ll expand out grade calculator that currently works to calculate the grade for just one student (the one whose data is in row 2) – to work for many students (one per row). This will give you a chance to see some of the power of relative and absolute addressing.

<< Back Next >>