An alternate method to Extract the Heading Values of your Sakai Grade Book - a method that does not use VBA Macros. Follow along with Bill Jelen as he provides a Formula solution to extracting those bracketed Values in Episode #1542. 'Livening' up your Grade Book. This is Part II 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!
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 # 152.
Sakai Grade Book Extract Points Possible from Square bracket [ 10.0 ] Hey welcome back. It's another MrExcel Podcast.
I'm Bill Jelen from MrExcel.com Yesterday we talked about a Macro that would help to take this grade book data and snap it into shape.
Today though if you don't want to use the Macro, maybe your grade book isn't exactly like the Sakai grade book.
We're going to take a look at some formulas.
First of all extracting the points possible from the headings.
I want you to take a good look at these headings here in the formula bar.
All right, so the column's not wide enough to see the whole thing.
Quiz C #1 [ 4.0 ] That's the number of points possible in that quiz and here there were 7 points possible and 4 and 5.0 and 10.0 and 0.01 All right, so all kinds of different links of the points in there.
But nicely, they all have the square bracket and we can use that to our advantage, so we're going to say Points here. We are going to create a student column Points.
=MID. The MID function of C1 , where we start while we're going to use another function called FIND.
The FIND of the left square bracket within C1.
Close parentheses +1 So I want to go find that square bracket go 1 character past it and then how many characters we want?
Well, I don't know I need five six seven.. Who knows? I'm just going to put a large number in there and it will give me to the end of the string up and see 1.
So now we've gotten a really good at getting the points possible plus a square bracket.
To get rid of that square bracket, I'm going to use the SUBSTITUTE function.
So I'm going to come back here and edit this formula and I'm going to say = SUBSTITUTE All right.
The string that we have is the result that we just came up with and I'm going to substitute every occurance of the right square bracket with " " Okay and so that says, hey take that right square bracket, get rid of it.
and now when I press Ctrl + Enter and it will copy it across.
It starts to look good, the problem is though even though it looks good, this is still text.
It's not numbers and I can tell that by looking down here at the status bar. It says Count: 5 If those were really numeric, I would have a COUNT and I would have a SUM.
The fact that it's giving just a Count tells me that it's text.
So we'll edit this one more time.
We'll just say +0 adding the 0 to that text that looks like a number will convert it to a real number and see down here, we now actually have a SUM.
All right, so we take that formula. It's working Copy it all the way across out to the last final there and column A E All right now that we have that and see we're going to insert, I'm going to insert four columns here.
So "Alt + I + C" for Inset column and we're going to put something called Total. All right Total.
Now I just know that all of these numbers are filled in. That's a really good thing so I can just use the AutoSum All right, and it's safe to use the AutoSum up here again because I know that everything is filled in.
However if I was doing it for the students, that would not be a good thing to do.
Because some of the students have blanks and then the AutoSum might stop when it encounters a blank so, as soon as you hit that AutoSUM you want to make sure that it's going all the way back to column C.
And now I'm going to grab that square dot and drag down.
Actually in excel 2010, I could have just double-clicked but earlier versions of Excel wouldn't do well, because of that the blank cells to the left. All right, so there we go now we know 422 total points possible and the score of each individual student.
Tomorrow we're going to take that and figure out what the percentage is.
Now there's a wrinkle here, and tomorrow's formula is actually going to be a lot more complicated than today's formula.
Well hey, I want to thank you for stopping by. See you next time for another netcast from MrExcel.
Sakai Grade Book Extract Points Possible from Square bracket [ 10.0 ] Hey welcome back. It's another MrExcel Podcast.
I'm Bill Jelen from MrExcel.com Yesterday we talked about a Macro that would help to take this grade book data and snap it into shape.
Today though if you don't want to use the Macro, maybe your grade book isn't exactly like the Sakai grade book.
We're going to take a look at some formulas.
First of all extracting the points possible from the headings.
I want you to take a good look at these headings here in the formula bar.
All right, so the column's not wide enough to see the whole thing.
Quiz C #1 [ 4.0 ] That's the number of points possible in that quiz and here there were 7 points possible and 4 and 5.0 and 10.0 and 0.01 All right, so all kinds of different links of the points in there.
But nicely, they all have the square bracket and we can use that to our advantage, so we're going to say Points here. We are going to create a student column Points.
=MID. The MID function of C1 , where we start while we're going to use another function called FIND.
The FIND of the left square bracket within C1.
Close parentheses +1 So I want to go find that square bracket go 1 character past it and then how many characters we want?
Well, I don't know I need five six seven.. Who knows? I'm just going to put a large number in there and it will give me to the end of the string up and see 1.
So now we've gotten a really good at getting the points possible plus a square bracket.
To get rid of that square bracket, I'm going to use the SUBSTITUTE function.
So I'm going to come back here and edit this formula and I'm going to say = SUBSTITUTE All right.
The string that we have is the result that we just came up with and I'm going to substitute every occurance of the right square bracket with " " Okay and so that says, hey take that right square bracket, get rid of it.
and now when I press Ctrl + Enter and it will copy it across.
It starts to look good, the problem is though even though it looks good, this is still text.
It's not numbers and I can tell that by looking down here at the status bar. It says Count: 5 If those were really numeric, I would have a COUNT and I would have a SUM.
The fact that it's giving just a Count tells me that it's text.
So we'll edit this one more time.
We'll just say +0 adding the 0 to that text that looks like a number will convert it to a real number and see down here, we now actually have a SUM.
All right, so we take that formula. It's working Copy it all the way across out to the last final there and column A E All right now that we have that and see we're going to insert, I'm going to insert four columns here.
So "Alt + I + C" for Inset column and we're going to put something called Total. All right Total.
Now I just know that all of these numbers are filled in. That's a really good thing so I can just use the AutoSum All right, and it's safe to use the AutoSum up here again because I know that everything is filled in.
However if I was doing it for the students, that would not be a good thing to do.
Because some of the students have blanks and then the AutoSum might stop when it encounters a blank so, as soon as you hit that AutoSUM you want to make sure that it's going all the way back to column C.
And now I'm going to grab that square dot and drag down.
Actually in excel 2010, I could have just double-clicked but earlier versions of Excel wouldn't do well, because of that the blank cells to the left. All right, so there we go now we know 422 total points possible and the score of each individual student.
Tomorrow we're going to take that and figure out what the percentage is.
Now there's a wrinkle here, and tomorrow's formula is actually going to be a lot more complicated than today's formula.
Well hey, I want to thank you for stopping by. See you next time for another netcast from MrExcel.
