Christina asks how to create a gradebook in Excel. Episode 506 shows off how to create absolute references and also the obscure TRUE version of the VLOOKUP function. Everyone teachers and non-teachers should check out the VLOOKUP in this episode.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Christina. Christina I'm assuming is a teacher.
She said how can we set up a student grade book in excel and so I've done a little bit of the work in advance here. Basically Christina what you want to do, It was set up some rows down here at the bottom where you have all your students.
I'm running down column A. Now I left some space at the top and the very top row is where I'm going to show the number of points that were possible for each assignment, and then in row 2. I'm just to help me remember, I have a list you know, This is quiz 1 homework 1 homework 2 or homework 3 etc and I basically left enough room out there for however many items I think I might give in one grading period.
So you know you might go out to cell B Z or whatever just depending on how many assignments you think you might have.
As you do the assignments don't fill in the total points possible until you've created everything.
So for example, let's say that we have another homework, HW4. This was worth 10 points, and then we can fill in everyone's scores.
Let's say that everyone received a 9 today.
Over on the left-hand side. I have three formula columns here. The first formula is the total of all the points so we use the =SUM formula.
Basically I'm going to go from cell E.
I'm going to go all the way out to the right however far I think I may go.
So in my case, I went out to BC, and I'll total those up.
You'll see there's 165 points possible so far. Now I want to copy that formula so Ctrl C.
I'll copy that down to all of my students, and I'll be able to see what their total is.
Next I want to figure out their percentage score.
The percentage score. This is an interesting formula because we want to point to cell B5 and allow that cell to change to B6 B7 B8. That's called a relative reference so =B5 but I want to divide by always cell B1.
So right now that I've typed the B1, I hit the F4 key and excel puts in $B$1. This tells excel that this is an absolute reference and no matter what we want to make sure that we point to cell B1 I want to format that as a percent so I use percent indicator and maybe the increased decimal so I can see whether it's 92.7 or 93.
Copy that formula down the fast way to copy down is to double-click the fill handle.
Next we have to set up our grading scale, and we're going to use a function called VLOOKUP.
VLOOKUP is an interesting function.
In this case usually in accounting we use Vlookup where we need an exact match, but in science or in this gradebook example we will have ranges and basically I need you to put your grading scale in a bit backwards.
So the way that it works is everyone who has a 0 or above gets an F.
Anyone with 0.6 or above gets a D.
Anyone with 0.7 or above gets a C.
0.85 or above gets a B and then point 0.92 or above gets an A.
Now obviously you might have a different grading scale with pluses and minuses but you start at the bottom and work up.
Then we're going to use the VLOOKUP function =VLOOKUP that stands for a vertical look up. We want to go look up this score. We want to look up Abby's 92.7% within the range that contains our grading scale.
Want to make sure the percentages are in the first column and the grades are in the second column and again we want that to be an absolute reference.
So I'll hit F4 and it puts dollar signs throughout.
Tell excel which Column we want, we want the second column and then finally put the word true. Ofcourse this is optional you could leave off true and it would default to true. Hit enter and we see that Abby has a name.
When I copy that down, it'll go through and figure out the scores for each student individually.
Now some other things we do, we might want to make sure that we can always see the student names and so I might put the cell pointer right there on B5 and go to window Freeze panes then as we go further along in the grading period and I'm scrolling over, I can still see which student I'm working on.
So that's basically the tips for creating a simple gradebook in excel.
Now all of these concepts are in the book Excel for Teachers, which you can get at MrExcel.com or Amazon or your local bookseller.
Want to thank Christina for writing in. Hopefully going through all of these steps helped.
I realize I went through it fast. You probably have to replay it again or check out the book.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Christina. Christina I'm assuming is a teacher.
She said how can we set up a student grade book in excel and so I've done a little bit of the work in advance here. Basically Christina what you want to do, It was set up some rows down here at the bottom where you have all your students.
I'm running down column A. Now I left some space at the top and the very top row is where I'm going to show the number of points that were possible for each assignment, and then in row 2. I'm just to help me remember, I have a list you know, This is quiz 1 homework 1 homework 2 or homework 3 etc and I basically left enough room out there for however many items I think I might give in one grading period.
So you know you might go out to cell B Z or whatever just depending on how many assignments you think you might have.
As you do the assignments don't fill in the total points possible until you've created everything.
So for example, let's say that we have another homework, HW4. This was worth 10 points, and then we can fill in everyone's scores.
Let's say that everyone received a 9 today.
Over on the left-hand side. I have three formula columns here. The first formula is the total of all the points so we use the =SUM formula.
Basically I'm going to go from cell E.
I'm going to go all the way out to the right however far I think I may go.
So in my case, I went out to BC, and I'll total those up.
You'll see there's 165 points possible so far. Now I want to copy that formula so Ctrl C.
I'll copy that down to all of my students, and I'll be able to see what their total is.
Next I want to figure out their percentage score.
The percentage score. This is an interesting formula because we want to point to cell B5 and allow that cell to change to B6 B7 B8. That's called a relative reference so =B5 but I want to divide by always cell B1.
So right now that I've typed the B1, I hit the F4 key and excel puts in $B$1. This tells excel that this is an absolute reference and no matter what we want to make sure that we point to cell B1 I want to format that as a percent so I use percent indicator and maybe the increased decimal so I can see whether it's 92.7 or 93.
Copy that formula down the fast way to copy down is to double-click the fill handle.
Next we have to set up our grading scale, and we're going to use a function called VLOOKUP.
VLOOKUP is an interesting function.
In this case usually in accounting we use Vlookup where we need an exact match, but in science or in this gradebook example we will have ranges and basically I need you to put your grading scale in a bit backwards.
So the way that it works is everyone who has a 0 or above gets an F.
Anyone with 0.6 or above gets a D.
Anyone with 0.7 or above gets a C.
0.85 or above gets a B and then point 0.92 or above gets an A.
Now obviously you might have a different grading scale with pluses and minuses but you start at the bottom and work up.
Then we're going to use the VLOOKUP function =VLOOKUP that stands for a vertical look up. We want to go look up this score. We want to look up Abby's 92.7% within the range that contains our grading scale.
Want to make sure the percentages are in the first column and the grades are in the second column and again we want that to be an absolute reference.
So I'll hit F4 and it puts dollar signs throughout.
Tell excel which Column we want, we want the second column and then finally put the word true. Ofcourse this is optional you could leave off true and it would default to true. Hit enter and we see that Abby has a name.
When I copy that down, it'll go through and figure out the scores for each student individually.
Now some other things we do, we might want to make sure that we can always see the student names and so I might put the cell pointer right there on B5 and go to window Freeze panes then as we go further along in the grading period and I'm scrolling over, I can still see which student I'm working on.
So that's basically the tips for creating a simple gradebook in excel.
Now all of these concepts are in the book Excel for Teachers, which you can get at MrExcel.com or Amazon or your local bookseller.
Want to thank Christina for writing in. Hopefully going through all of these steps helped.
I realize I went through it fast. You probably have to replay it again or check out the book.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
