Todays Dueling Excel Podcast, Episode #1286, addresses a teacher's question of how to calculate the % score for a student, ignoring any assignments where the student had an excused absence. SUMPRODUCT, ISNUMBER and More in today's episode!
Transcript of the video:
Hey, welcome back! It's another dueling Excel podcast.
I am Bill Jelen from MrExcel joined by Mike Girvin from Excel Is Fun.
Today's topic is Excu... I assume this is from a professor or teacher who said, "Hey I want to calculate the percentage correct but if the student was ill or not there, something that I don't want to include the score in the denominator". All right, so let's take a look at this of course to get the totals, that's easy.
Just hit Alt equals and it will add the SUM function there but here's where the hard part comes in, we need to figure out the denominator. What we're going to divide by. I'm just going to come down here and build this formula. Actually, will do it out here equal. I am going to use SUMPRODUCT, SUMPRODUCT and what I'm going to do is I want to take all of the values up there and our scores. I wanna hit F4 to lock that down and multiply that by the results of an if or ISNUMBER. Okay! now, ISNUMBER don't return a bunch of true's and false's. I need to convert those true's and false's into 1's and 0's. So, I'm going to do the minus, minus trick.
ISNUMBER And tab there and check to see if these scores over here are numeric. All right, so we'll close the ISNUMBER. Close the SUMPRODUCT. You see that here it's adding up the 10 and the 15 because the person was ill there. So because that's not numeric, I'm getting a zero from the ISNUMBER x 5 is giving me zero.
Will copy this up. We should see and we get 30 for students A because they were there for all of the possible events. All right, so now that we have this formula, copy that to clipboard. Ctrl+C and we do equal.
This value divided by Ctrl+V and bam!
72%, I am going to copy it up. 73.3 because there are 22 divided by 30.
All right, so that's it using SUMPRODUCT for the denominator. Mike let's see what you have.
Thanks MrExcel! Hey god! I love that ISNUMBER, SUMPRODUCT. I like that ISNUMBER because when I hit Enter, I ain't come over here and it's asking the question "Are any of these numbers?" Right?
and if I could type in anything, I could take the letter O, NE for Not Here or even check this out a blank.
So, anytime, now, if you wanted to include this as a zero because they got a zero. You'd have to type a zero and then sure enough 60% because 18 / 30. Now, let's assume in this situation, I like this one.
I can't really improve on that because if you have it blank, right? And you don't want that one included, ISNUMBER works but let's try something slightly different. I'm going to assume that a blank here, would mean they didn't take the test and not until we actually type a word like, "ill" or "Not Here" or whatever, do we need to exclude this? All right, I need to total. So, I'm going to click there and Alt equals, Ctrl+Enter and double click and send it down. Now for the denominator, I'm going to use SUMIF.
SUMIF is great. Range, that's the range that has the criteria. In our case, we're just going to highlight this and when we copy it down, it'll move as a relative cell reference. Comma criteria, I'm going to say try not a double quote. Not is less than greater than. Screen tips in the way and then "ill". All right so, there's criteria. Anytime it finds anything that's not ill like these numbers, it will add them comma and the sum_range.
Now, this range right here is called Range and it actually determines the size of this. So, if I highlight this or this, it doesn't matter. Not only just look at this again, this is a three columns one row. So when I click here like this, just that first cell it'll know that it's supposed to be one row three columns.
All right and I'm going to hit F4. Close parentheses Ctrl+Enter.
Double click and send it down.
So that one's working.
Obviously now, if I type something like that, then it's not going to work. So, I'm going to try and amend this. Actually, I'll leave it like that. Come up here and instead of "not ill" I'm going to say "not text". And the wildcard asterisks says any sequence of characters. So that means any time we type any text, even a space from now on, it will register. So I'm going to double click and send this down. So now, I can type that a space or even ill, right.
And so what I'm thinking here is if we leave it blank, which means we haven't entered any text then, we want to count all of these, right? because they haven't turned in an absence slip or something.
All right, so we can take this copy, Esc, Edit mode, Ctrl+V and then I'm going to click right here.
Divided by Ctrl+Enter double click and send it down. So now, when this person turns to note, You know, whatever is there...
All right, thrown over to MrExcel.
I am Bill Jelen from MrExcel joined by Mike Girvin from Excel Is Fun.
Today's topic is Excu... I assume this is from a professor or teacher who said, "Hey I want to calculate the percentage correct but if the student was ill or not there, something that I don't want to include the score in the denominator". All right, so let's take a look at this of course to get the totals, that's easy.
Just hit Alt equals and it will add the SUM function there but here's where the hard part comes in, we need to figure out the denominator. What we're going to divide by. I'm just going to come down here and build this formula. Actually, will do it out here equal. I am going to use SUMPRODUCT, SUMPRODUCT and what I'm going to do is I want to take all of the values up there and our scores. I wanna hit F4 to lock that down and multiply that by the results of an if or ISNUMBER. Okay! now, ISNUMBER don't return a bunch of true's and false's. I need to convert those true's and false's into 1's and 0's. So, I'm going to do the minus, minus trick.
ISNUMBER And tab there and check to see if these scores over here are numeric. All right, so we'll close the ISNUMBER. Close the SUMPRODUCT. You see that here it's adding up the 10 and the 15 because the person was ill there. So because that's not numeric, I'm getting a zero from the ISNUMBER x 5 is giving me zero.
Will copy this up. We should see and we get 30 for students A because they were there for all of the possible events. All right, so now that we have this formula, copy that to clipboard. Ctrl+C and we do equal.
This value divided by Ctrl+V and bam!
72%, I am going to copy it up. 73.3 because there are 22 divided by 30.
All right, so that's it using SUMPRODUCT for the denominator. Mike let's see what you have.
Thanks MrExcel! Hey god! I love that ISNUMBER, SUMPRODUCT. I like that ISNUMBER because when I hit Enter, I ain't come over here and it's asking the question "Are any of these numbers?" Right?
and if I could type in anything, I could take the letter O, NE for Not Here or even check this out a blank.
So, anytime, now, if you wanted to include this as a zero because they got a zero. You'd have to type a zero and then sure enough 60% because 18 / 30. Now, let's assume in this situation, I like this one.
I can't really improve on that because if you have it blank, right? And you don't want that one included, ISNUMBER works but let's try something slightly different. I'm going to assume that a blank here, would mean they didn't take the test and not until we actually type a word like, "ill" or "Not Here" or whatever, do we need to exclude this? All right, I need to total. So, I'm going to click there and Alt equals, Ctrl+Enter and double click and send it down. Now for the denominator, I'm going to use SUMIF.
SUMIF is great. Range, that's the range that has the criteria. In our case, we're just going to highlight this and when we copy it down, it'll move as a relative cell reference. Comma criteria, I'm going to say try not a double quote. Not is less than greater than. Screen tips in the way and then "ill". All right so, there's criteria. Anytime it finds anything that's not ill like these numbers, it will add them comma and the sum_range.
Now, this range right here is called Range and it actually determines the size of this. So, if I highlight this or this, it doesn't matter. Not only just look at this again, this is a three columns one row. So when I click here like this, just that first cell it'll know that it's supposed to be one row three columns.
All right and I'm going to hit F4. Close parentheses Ctrl+Enter.
Double click and send it down.
So that one's working.
Obviously now, if I type something like that, then it's not going to work. So, I'm going to try and amend this. Actually, I'll leave it like that. Come up here and instead of "not ill" I'm going to say "not text". And the wildcard asterisks says any sequence of characters. So that means any time we type any text, even a space from now on, it will register. So I'm going to double click and send this down. So now, I can type that a space or even ill, right.
And so what I'm thinking here is if we leave it blank, which means we haven't entered any text then, we want to count all of these, right? because they haven't turned in an absence slip or something.
All right, so we can take this copy, Esc, Edit mode, Ctrl+V and then I'm going to click right here.
Divided by Ctrl+Enter double click and send it down. So now, when this person turns to note, You know, whatever is there...
All right, thrown over to MrExcel.
