In Part I, Bill looks at the 'Sakai Grade Book System and how to take the .CSV data file that it provides for teachers and turn it into a 'Live Grade Book' - so that scores can be updated and the proper grade reflected. Since formulas are not used in .CSV, Bill has produced a series of Podcasts and a 'Grade Book App'. Starting today, with Episode #1541, lets look into 'Sakai', how the core data appears and where to start 'Livening' up your Grade Book. This is Part I of IV.
...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!
...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:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Episode 1541: Sakai Grade Boo Macro.
Alright.
So, over the weekend, I got a call from a good friend of mine and said, “Boy this sounds like a really easy thing to do in Excel.
But, I’ve asked a lot of my co-workers and we can’t figure out.
They--” She’s a teacher and she has-- the school has a gradebook system called Sakai.
And Sakai downloads the CSV file that has all the grades.
But, of course, because it’s a CSV file, the final score out here is not a live formula.
It’s just numbers.
Right, that’s all we can have in a CSV file.
And so, the question is how we can make this into a live grade book that we can update scores here and get the grade.
I said, “Alright, that sounds like an easy question.
But, it’s not.
We’re actually going to spend this week in the podcast going through various formulas for us to do this.” But, first, I want to offer.
If you don’t want to go through all the formulas.
If I just want a solution for it.
I have a macro that will solve this problem for Sakai.
Alright.
And this is free.
Alright.
Let me go out to-- let’s see here.
I got to mrexcellab.com/gradebook.
You can have it.
I have huge respect for teachers.
No reason to charge teachers for this.
Alright.
If your copy of Excel does not allow macros, you can do the little test.
Macros here it says macro may not be available int this workbook.
There’re instructions here on how to get macros enable.
Choose Disable with notification.
Click OK.
Now, of course, we have to close this file.
And then reopen it.
The good thing is once you’ve done this, so, now your macros have been disabled.
Now, I have the choice to enable the content.
Alright.
Here we go.
I have the macro available.
By the way, the formulas here are from Bill Haslett.
He has a book called Excel for Teachers.
I’m a co-author on that book.
But, I’m stealing from Bill’s Chapter 6 on the gradebook.
If you download the macro, feel free to click through this and you know it’s an e-book or a print book.
Support Bill for giving us these formulas.
So, keep this workbook open in the background.
Open the gradebook.
Download it from Sakai and then CTRL + SHIFT + G to add formulas.
Someone uses CTRL + TAB.
Here’s my data from Sakai.
Notice that it’s a CSV file right now.
CTRL + SHIFT + G.
There.
It actually adds in the total points, the possible points, and we’ll talk about that on Wednesday, the percentage, and then, calculates the letter right now.
If you’re grading scale is not the same as my friend’s grading scale, you can edit it here in this orange block.
Just make sure that goes from lowest to highest.
And then, also down at the bottom, it adds a little frequency distribution.
So, here.
We can see there’s 14 Cs, 5 Bs, only one A. And so on, that’s also-- it sets the print range though.
We can just print this out and post it for your students if you care to do that.
Alright.
That’s the macro way to do it.
Tomorrow, we’re going to take a look at the formulas.
The formulas figure out the number of points possible which was pretty tricky to do at the top.
And then, also, Sakai does something interesting.
If the person was absent, this date, it does not include these assignments in their total.
And so, this person who was absent a few days, only has possible up to 250.
We’ll take a look at that formula on Wednesday.
Go ahead.
I want to thank you for stopping by.
See you next time for another netcast of MrExcel.
Learn Excel from MrExcel Episode 1541: Sakai Grade Boo Macro.
Alright.
So, over the weekend, I got a call from a good friend of mine and said, “Boy this sounds like a really easy thing to do in Excel.
But, I’ve asked a lot of my co-workers and we can’t figure out.
They--” She’s a teacher and she has-- the school has a gradebook system called Sakai.
And Sakai downloads the CSV file that has all the grades.
But, of course, because it’s a CSV file, the final score out here is not a live formula.
It’s just numbers.
Right, that’s all we can have in a CSV file.
And so, the question is how we can make this into a live grade book that we can update scores here and get the grade.
I said, “Alright, that sounds like an easy question.
But, it’s not.
We’re actually going to spend this week in the podcast going through various formulas for us to do this.” But, first, I want to offer.
If you don’t want to go through all the formulas.
If I just want a solution for it.
I have a macro that will solve this problem for Sakai.
Alright.
And this is free.
Alright.
Let me go out to-- let’s see here.
I got to mrexcellab.com/gradebook.
You can have it.
I have huge respect for teachers.
No reason to charge teachers for this.
Alright.
If your copy of Excel does not allow macros, you can do the little test.
Macros here it says macro may not be available int this workbook.
There’re instructions here on how to get macros enable.
Choose Disable with notification.
Click OK.
Now, of course, we have to close this file.
And then reopen it.
The good thing is once you’ve done this, so, now your macros have been disabled.
Now, I have the choice to enable the content.
Alright.
Here we go.
I have the macro available.
By the way, the formulas here are from Bill Haslett.
He has a book called Excel for Teachers.
I’m a co-author on that book.
But, I’m stealing from Bill’s Chapter 6 on the gradebook.
If you download the macro, feel free to click through this and you know it’s an e-book or a print book.
Support Bill for giving us these formulas.
So, keep this workbook open in the background.
Open the gradebook.
Download it from Sakai and then CTRL + SHIFT + G to add formulas.
Someone uses CTRL + TAB.
Here’s my data from Sakai.
Notice that it’s a CSV file right now.
CTRL + SHIFT + G.
There.
It actually adds in the total points, the possible points, and we’ll talk about that on Wednesday, the percentage, and then, calculates the letter right now.
If you’re grading scale is not the same as my friend’s grading scale, you can edit it here in this orange block.
Just make sure that goes from lowest to highest.
And then, also down at the bottom, it adds a little frequency distribution.
So, here.
We can see there’s 14 Cs, 5 Bs, only one A. And so on, that’s also-- it sets the print range though.
We can just print this out and post it for your students if you care to do that.
Alright.
That’s the macro way to do it.
Tomorrow, we’re going to take a look at the formulas.
The formulas figure out the number of points possible which was pretty tricky to do at the top.
And then, also, Sakai does something interesting.
If the person was absent, this date, it does not include these assignments in their total.
And so, this person who was absent a few days, only has possible up to 250.
We’ll take a look at that formula on Wednesday.
Go ahead.
I want to thank you for stopping by.
See you next time for another netcast of MrExcel.
