Sometimes you want to score people, not just on whether they've answered the question
“yes” or “no”, but you want to give some questions more weight: weighted scoring. So,
here I've got a series of questions one to ten, and people may answer the question at a basic
level. And so they get one point. Or maybe at an intermediate level or if it's a really difficult
question they can do it at the advanced level. And so they may answer it just at the basic level and
they get one point but if they answer it at the intermediate level they get three points because
that's worth two (one plus two). And then if they do the advanced one it's worth ten points. If
they just jump straight to the advanced then they still get seven points, even if they don't do the
basic answers. So, how do we set all of this up? I'm going to show you how to do that now,
so you can open a new spreadsheet in Google Sheets and to do that you go to sheet.new
We're going to go basic, intermediate, advanced. And now, we want to freeze that row.
And then what is the exercise itself?
And we can just drag that down and Google Sheets will continue the pattern.
And now we want to freeze that column.
So we want to get these checkboxes in here. So click on here, down here, and hit
hold the shift key down and press click. Insert checkbox. And now, just to keep the colors
consistent, we're going to make the yellow here and we could make it the background but in fact I
think that looks a little bit too cluttered so I'm going to clear that and make it actually the text.
I'll make that a bit darker
and now the intermediate.
And then click on here for the advanced. Hold the shift key down. Select.
So far, so good, but how do you get these checkboxes to be worth one point or two
points or seven points? Well, when I click on the checkbox by default have a look up here
that's set to true and when I click it again
that's set to FALSE. Actually, what I wanted to do is to be worth a value. So what I do is I click
from here down to here. Select. And then I go “Data” or you may say data. And what I want to
do is validate this data and I want to say “Use custom cell values”. Now it's still yes or no.
It's still a binary value which means that it's either this or it's that either true
or false. There's no range in between. So, what I can do is I click on Use custom cell values,
and I say if this cell is checked then that is worth one point, otherwise it's worth zero.
Click Save. And now I can do that for this one here to make it worth two points.
And then this one is worth seven points.
Now, if I add those up, well I just go “sum”
from that to that to that: sum b3 to d3. And okay, that's worth zero points but if I click on here
that's worth one point. If I click on here, it's worth three points: one plus two.
I can uncheck that so maybe somebody answers the intermediate question but skips the basic question
and then if we get a 7 then and if they click all three then it's worth 10 points.
And so, what I can do is just drag that formula down to here
and as I click on these, just pick a few random numbers. Here you can see how this works.
And of course you can then put in the total here and then you can put in a total here. So that you
know how many basic questions they get, how many of the intermediate and how many of the advanced?
So, it's a sum.
Three. And those totals should match, so I can just do some here this column down to here.
And that's 28. And so, as you see, if I click on these...all right, let me make it easier. Okay, so
if we say somebody has just answered mainly the basic questions I get one point for each one
and this keeps adding up the total if they do an intermediate question as well then they get
another two points and you can see how that running total just keeps increasing. So once
again you create the checkboxes first and then for the checkboxes you select data, data validation.
And then you say what you use custom cell values and if the box is checked then it's worth
whatever your value is and if it's unchecked then this is what it's worth.