Embedded Files
Now let’s make a grade sheet – where we calculate the grades for more that one student! Just to let you know – you’ve already done most of the hard work! This should be a lot easier and take less time and effort for you – even as you make the computer do lots more work.
Column A is going to be the column that stores the names of our students. Change the text in column A2 from CSE 3 to Chu, add the names Michael, Elizabeth, Sarah, and Gabby to the list – in A3, A4, A5, and A6.
Finally – enter the grades for each of the students in the correct columns. Remember – you don't enter data in Clicker1P (and some other columns). Be sure to enter the data in the columns where the “raw” scores go (F, G, H, I, J, K, M)
List for Michael’s Grades {
Clicker 1 : 4
Clicker 2 : 7
Clicker 3 : 8
Lab 1 : 15
Lab 2 : 2
Lab 3 : 9
Midterm: None
}
List for Elizabeth’s Grades {
Clicker 1 : 8
Clicker 2 : 7
Clicker 3 : 8
Lab 1 : 15
Lab 2 : 13
Lab 3 : 14
Midterm : 27
}
List for Sarah’s Grades {
Clicker 1 : 9
Clicker 2 : 4
Clicker 3 : 5
Lab 1 : 11
Lab 2 : 12
Lab 3 : 13
Midterm : 24
}
List for Gabby’s Grades {
Clicker 1 : 12
Clicker 2 : 0
Clicker 3 : 5
Lab 1 : 9
Lab 2 : 15
Lab 3 : 11
Midterm : 26
}
Now let’s fill vertically for Clicker1P (Drag the fill down from cell B2 to cell B6) and repeat for Clicker2P, Clicker3P, ClickerAvg, LabAvg, WeightedTotal, and CurrentTotal.
Your sheet should look something like this.
Let’s consider some new things we might want to calculate about our data. This will show us some other useful spreadsheet functions. In many different careers and jobs being able to ask questions about a set of data you have is really invaluable. If someone else has to do it by hand and you can do it with a calculation – you’ll be more reliable and faster (if you have more than a teeny amount of data).
How would we count how many students are there?
How about students with the letter e in their name?
You could imagine how things like this could be important information for someone to know (though we’re not too sure about “letter e in the name”). We can use functions similar to COUNT to answer these questions: COUNTA and COUNTIF.
COUNTA will count all the cells that are filled (e.g. have some value in them), regardless of whether they’re numbers or not.
It uses the same syntax as COUNT, where it’s either using individual cells or a range of cells.
=COUNTA(Cell1,Cell2) or =COUNTA(Cell1:Cell5)
So let’s count how many students we have (remember – imagine LARGE classes – that’s where this would be useful. MOOCs have sometimes over ½ a million students enrolled. That’s large.)
=COUNTA(A2:A7)
What about conditional counting – where you want to count some things, but not others? What about counting just students with the letter e in their name? We can do that by using COUNTIF.
Similar to a combination of IF and COUNT, COUNTIF uses only two parameters, a range of cells and a Boolean criteria (an expression that evaluates to true or false). Criteria is a spreadsheet term they think is easier for people to understand than Boolean expression. But really, it’s a Boolean expression. Now that you know what that is from Alice criteria is probably not as helpful a description.
=COUNT(range,criteria).
As a quick simple example we can check how many students in the class are named Chu
=COUNTIF(A2:A7,”Chu”)
In order to do something more complex like how many students in the class have the letter e in their name – we’ll learn to use something called a wildcard.
A wildcard is a star * (shift-8 on your keyboard). You use wildcards as part of a string of text to say that you want to match anything to the * part of the text. What?
For example if you put it as a parameter for COUNTIF as *h, the function will only return Elizabeth and Sarah – because they are the only names that end in h. The *h means anything and then an h.
If you put it as h*, the function will return nothing. That would match anything that starts with h. Like Harold or Howie.
[Q13.7.1] What name would match both *h and h*?
If you put it as *h*, the function will return all the names except Gabby, because all our other names have an h somewhere in the middle. Actually *h* also matches Hannah – because the wildcard can match with nothing. As in no character before the h. Or no character after the h.
So to check how many students have the letter e in their name, we can just do =COUNTIF(A2:A7, “*e*”)
Page updated
Report abuse