Today, in the last installment of the Sakai Grade Book series, Bill looks at converting the Percentage Score in the Grade Book to a Letter Grade.
Using a baseline, a Named Range and a Formula - which Bill shares with us today - your Dynamic Grade Book Project will be complete. Follow along with Bill in Episode #1544 to learn how to implement the finishing touches! 'Livening' up your Grade Book. This is Part IV of IV.
Formulas for the 'Grade Book Application' that Bill Jelen has created are from the book, "Excel For Teachers" [ Excel for Teachers ] published by Holy Macro! Books. Learn more about the book "Excel For Teachers" by clicking here [ Excel for Teachers ] to go to the "Excel For Teachers" information page.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Using a baseline, a Named Range and a Formula - which Bill shares with us today - your Dynamic Grade Book Project will be complete. Follow along with Bill in Episode #1544 to learn how to implement the finishing touches! 'Livening' up your Grade Book. This is Part IV of IV.
Formulas for the 'Grade Book Application' that Bill Jelen has created are from the book, "Excel For Teachers" [ Excel for Teachers ] published by Holy Macro! Books. Learn more about the book "Excel For Teachers" by clicking here [ Excel for Teachers ] to go to the "Excel For Teachers" information page.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 1544: Sakai Gradebook Convert Percentage Score to Letter Grade Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
All week we’ve been talking about this gradebook project and today, Episode 1544, we’re going to talk about converting percentage scores to letter grades.
So let’s take a look at where we are right now.
We have this Percentage Score and we want to convert that to a Letter Grade.
And the way we're going to do that, we're to come out to the right somewhere and you always start the table like this.
You say, if you get a 0, you're going to get an F. Alright, and then the next thing you have to ask yourself is which score will get me some other grade, alright?
So, in your grading scale is it 60%, is it 65%?
Let's say it's 65.
I want to put: .65, you'll get a D; and then at 0.7 you'll get a C; 0.8 you'll get a B; and, 0.9 you'll get an A. And maybe you have more grades than this, maybe you have D- and D+ and so on.
The important point is that you create your table going from smallest to larger cell.
Always start at 0, and work your way up; and then - Excel is smart enough that, let’s say that someone has a .78, it’s going to find- it’s going to look for .78, it’s not going to find it and so it’s going to give us a value just smaller which is .7, which is the C. And make my life a little bit easier here, I'm going to select those cells and I'm just going to give it a name, I’ll click up here and call this Grades.
That name has to be a single word, no spaces.
So we have our name range now, =VLOOKUP of this % Score, in the Grades table, and then what’s the- what column do we want.
Alright, well we have column 1 is the Score, where we have that; column 2 is the Grades.
That’s what we want.
And there we go, converts that 85% to a B, we’ll copy down.
Alright, so there you go.
Over the course of the week, we've come up with several different formulas.
The nice thing about this, though, is now if you actually update the score - So here we are in row 7, this person currently has an F, they turn in their assignment.
They got a 35, now they’re up to a D. So, all of the formulas are live, you can, you know, you can enter your grades and see what the results are right here on the Fly.
Oh, here the most important thing, when we got the data back on Monday.
GradebookData.csv, well all of these cool formulas that we created, cannot live in a CSV file.
If I would save this as a CSV, all of those formulas going to change, the numbers, so that would be very frustrating.
Make sure to do: File, Save As- File, Save As, and then choose one of the Excel file formats.
Whatever you usually use now: XLSX, XLSM, XLSB, or if you're in the old version of Excel, even XLS.
Just choose one of those and make sure to save the data.
Maybe even say, you know, that this is a Period3 or something like that so we can differentiate it from the others.
Well, there you go.
A whole week's worth of the gradebook application.
Lots of different formulas including extracting the numbers from the square brackets up here, so we could figure out the points possible.
That huge formula from yesterday, the =SUM(IF(ISBLANK with Ctrl+Shift+Enter and then VLOOKUP today to convert that to a letter grade.
And hey, remember to follow that this is just a big hassle; you want to download the Macro.
It's absolutely free.
Go to mrexcellabs.com/gradebook and we'll let you use that macro that does all the steps that we just did over the course of this week but makes it just a simple keystroke Ctrl+Shift+G.
Well, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
All week we’ve been talking about this gradebook project and today, Episode 1544, we’re going to talk about converting percentage scores to letter grades.
So let’s take a look at where we are right now.
We have this Percentage Score and we want to convert that to a Letter Grade.
And the way we're going to do that, we're to come out to the right somewhere and you always start the table like this.
You say, if you get a 0, you're going to get an F. Alright, and then the next thing you have to ask yourself is which score will get me some other grade, alright?
So, in your grading scale is it 60%, is it 65%?
Let's say it's 65.
I want to put: .65, you'll get a D; and then at 0.7 you'll get a C; 0.8 you'll get a B; and, 0.9 you'll get an A. And maybe you have more grades than this, maybe you have D- and D+ and so on.
The important point is that you create your table going from smallest to larger cell.
Always start at 0, and work your way up; and then - Excel is smart enough that, let’s say that someone has a .78, it’s going to find- it’s going to look for .78, it’s not going to find it and so it’s going to give us a value just smaller which is .7, which is the C. And make my life a little bit easier here, I'm going to select those cells and I'm just going to give it a name, I’ll click up here and call this Grades.
That name has to be a single word, no spaces.
So we have our name range now, =VLOOKUP of this % Score, in the Grades table, and then what’s the- what column do we want.
Alright, well we have column 1 is the Score, where we have that; column 2 is the Grades.
That’s what we want.
And there we go, converts that 85% to a B, we’ll copy down.
Alright, so there you go.
Over the course of the week, we've come up with several different formulas.
The nice thing about this, though, is now if you actually update the score - So here we are in row 7, this person currently has an F, they turn in their assignment.
They got a 35, now they’re up to a D. So, all of the formulas are live, you can, you know, you can enter your grades and see what the results are right here on the Fly.
Oh, here the most important thing, when we got the data back on Monday.
GradebookData.csv, well all of these cool formulas that we created, cannot live in a CSV file.
If I would save this as a CSV, all of those formulas going to change, the numbers, so that would be very frustrating.
Make sure to do: File, Save As- File, Save As, and then choose one of the Excel file formats.
Whatever you usually use now: XLSX, XLSM, XLSB, or if you're in the old version of Excel, even XLS.
Just choose one of those and make sure to save the data.
Maybe even say, you know, that this is a Period3 or something like that so we can differentiate it from the others.
Well, there you go.
A whole week's worth of the gradebook application.
Lots of different formulas including extracting the numbers from the square brackets up here, so we could figure out the points possible.
That huge formula from yesterday, the =SUM(IF(ISBLANK with Ctrl+Shift+Enter and then VLOOKUP today to convert that to a letter grade.
And hey, remember to follow that this is just a big hassle; you want to download the Macro.
It's absolutely free.
Go to mrexcellabs.com/gradebook and we'll let you use that macro that does all the steps that we just did over the course of this week but makes it just a simple keystroke Ctrl+Shift+G.
Well, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
