Still working with the CSV Data from the Sakai Grade Book, Bill looks at Calculating the Total Possible Points from Non-Blank Cells. Do you need just the percentage scoring? Cannot figure out why your figures are so far off from the Sakai Total? In Episode #1543, Bill examines why this is happening and what you can do to quickly and effectively correct that issue. 'Livening' up your Grade Book. This is Part III 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 1543: Sakai Gradebook Sum When Not Blank Well, hey, welcome back, it’s Bill Jelen from MrExcel.com.
All this week, we’ve been talking about this gradebook data that we downloaded and how to make that into a nice live model where we can enter new grades.
Yet, 1541, free macro to do everything; 1542, how to get the points possible from the headings.
Today, I think, is going to be the hardest part: calculating the total possible points for the non-blank cells.
Let’s talk about what that means.
So, if I just needed to figure out the percentage score, 253 divided by the 422, I’ll press F4 here to put the dollar signs in, so that way it always refers back to that cell.
We would get our percentage score but the problem is the numbers that we’re calculating are not anywhere close to what Sakai is giving us.
And it turns out that Sakai has this rule that says, “Alright, hey, this student was not here for this assignment.
So, rather than put a 0 in, it’s blank and when it’s blank it doesn’t include that 40 in the points possible.
That’s the difference.” Oh my gosh, this is going to be an incredible formula.
So, I’m going to say, Possible, and this is the possible per student.
And what we’re going to do is we're going to have a big IF statement here: =IF, and we’re going to check to see: (ISBLANK).
Alright, (ISBLANK) we’re going to say, if everything from C3 to AE3 is blank, if it’s blank then I want to add a 0.
If it’s not blank, then I want to use the number from row 2.
So that’s C$2:AE$2.
Alright now, why the $ sign?
That says that no matter what student we’re in, whether we’re in row 3 or row 33, we’re always going to reach back up to row 2 to get the points possible.
But that’s only when the corresponding cell is not blank.
Alright now, this particular formula is going to return about 29 numbers to me.
Some of them will be 0, some of them will be numbers; but, you know, we can’t have a formula that returns 29 answers because we’re trying to fit it all in one cell.
So I need to wrap that formula in a =SUM function.
SUM function that’ll take all of those answers, all 29 answers and sum them up because this is returning an array of values.
We’re going to hold down Ctrl+Shift and then press Enter.
Ctrl+Shift+Enter let’s take a look.
Alright, so there’s our 298 and then we’ll figure out our % Score is the 253 divided by the 298, alright, 8506.
That looks very similar to what we’re getting there.
Alright, to copy this down.
Now that we have a nice contiguous column to the left, we’ll just double-click.
Alright, and it looks like all of our answers here are equivalent to the answers there.
This is a heck of a formula here.
This is called an array formula where it’s evaluating a whole bunch of cells and then passing it on to the SUM function.
Remember, Ctrl+Shift+Enter in order to pull this off.
Alright now, tomorrow we’ll take a look at how to convert this percentage score into a letter grade using VLOOKUP.
Oh hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
All this week, we’ve been talking about this gradebook data that we downloaded and how to make that into a nice live model where we can enter new grades.
Yet, 1541, free macro to do everything; 1542, how to get the points possible from the headings.
Today, I think, is going to be the hardest part: calculating the total possible points for the non-blank cells.
Let’s talk about what that means.
So, if I just needed to figure out the percentage score, 253 divided by the 422, I’ll press F4 here to put the dollar signs in, so that way it always refers back to that cell.
We would get our percentage score but the problem is the numbers that we’re calculating are not anywhere close to what Sakai is giving us.
And it turns out that Sakai has this rule that says, “Alright, hey, this student was not here for this assignment.
So, rather than put a 0 in, it’s blank and when it’s blank it doesn’t include that 40 in the points possible.
That’s the difference.” Oh my gosh, this is going to be an incredible formula.
So, I’m going to say, Possible, and this is the possible per student.
And what we’re going to do is we're going to have a big IF statement here: =IF, and we’re going to check to see: (ISBLANK).
Alright, (ISBLANK) we’re going to say, if everything from C3 to AE3 is blank, if it’s blank then I want to add a 0.
If it’s not blank, then I want to use the number from row 2.
So that’s C$2:AE$2.
Alright now, why the $ sign?
That says that no matter what student we’re in, whether we’re in row 3 or row 33, we’re always going to reach back up to row 2 to get the points possible.
But that’s only when the corresponding cell is not blank.
Alright now, this particular formula is going to return about 29 numbers to me.
Some of them will be 0, some of them will be numbers; but, you know, we can’t have a formula that returns 29 answers because we’re trying to fit it all in one cell.
So I need to wrap that formula in a =SUM function.
SUM function that’ll take all of those answers, all 29 answers and sum them up because this is returning an array of values.
We’re going to hold down Ctrl+Shift and then press Enter.
Ctrl+Shift+Enter let’s take a look.
Alright, so there’s our 298 and then we’ll figure out our % Score is the 253 divided by the 298, alright, 8506.
That looks very similar to what we’re getting there.
Alright, to copy this down.
Now that we have a nice contiguous column to the left, we’ll just double-click.
Alright, and it looks like all of our answers here are equivalent to the answers there.
This is a heck of a formula here.
This is called an array formula where it’s evaluating a whole bunch of cells and then passing it on to the SUM function.
Remember, Ctrl+Shift+Enter in order to pull this off.
Alright now, tomorrow we’ll take a look at how to convert this percentage score into a letter grade using VLOOKUP.
Oh hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
