I have created a grade sheet to record grades for my university students. That link is to One Drive where it can be viewed.
Background: I teach 7 different classes, that are each assigned a number. Each class and their grades occupy one sheet in the workbook. On the last page of the workbook, I have a breakdown of the grades showing the average scores for each assignment as well as a running count of students that have a certain letter grade. It is this running count that is giving me problems.
Desired outcome: On the last sheet, "averages", I want to be able to plug the current possible points into cell F3 (I plan to do this manually). Then, I would like excel to find the number of students that have under 60% of that value, an F, from each class (7 different sheets) and tell me the total number of students. I would like to repeat this process for 60%-64.99% (D-), 65-69.99% (D+) and so on.
Current work around: currently I am changing the formulas in column C, rows 6:14 manually after each assignment (very time consuming).
Problem: I think that this should use some sort of an INDIRECT function, but every combination I've tried has not yielded the correct outcome, possibly because the formula already includes an indirect function?
I would welcome any feedback as to the current formulas, because they are kinda messy it feels like. I've never taken a course on Excel, just done research online.
My formula for calculating the number of F's: =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=0")) - SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=59.99"))
This formula will count the number of students who scored higher than 0 (all of them) and subtract the number of students that scored a 60 (I would manually edit this number) or higher. Ideally, instead of "60", I would like the formula to be .60*F3, where F3 is the current possible points for the class.
My formula for calculating the number of D-'s: =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=60")) - SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=64.99"))
This formula will count the number of students who scored higher than 60 (I would manually edit this number) and subtract the number of students that scored a 65 (I would manually edit this number) or higher. Ideally, instead of "60", I would like the formula to be .60*F3, where F3 is the current possible points for the class, and instead of 64.99, it would be .6499*F3.
This repeats on down the line and works perfectly if there were 100 possible points all the time. But, of course, this is not the case.
I hope this explanation was clear. It's a little difficult for me to explain. Any feedback on how to improve this workbook would be greatly appreciated. Thank you in advance.
Background: I teach 7 different classes, that are each assigned a number. Each class and their grades occupy one sheet in the workbook. On the last page of the workbook, I have a breakdown of the grades showing the average scores for each assignment as well as a running count of students that have a certain letter grade. It is this running count that is giving me problems.
Desired outcome: On the last sheet, "averages", I want to be able to plug the current possible points into cell F3 (I plan to do this manually). Then, I would like excel to find the number of students that have under 60% of that value, an F, from each class (7 different sheets) and tell me the total number of students. I would like to repeat this process for 60%-64.99% (D-), 65-69.99% (D+) and so on.
Current work around: currently I am changing the formulas in column C, rows 6:14 manually after each assignment (very time consuming).
Problem: I think that this should use some sort of an INDIRECT function, but every combination I've tried has not yielded the correct outcome, possibly because the formula already includes an indirect function?
I would welcome any feedback as to the current formulas, because they are kinda messy it feels like. I've never taken a course on Excel, just done research online.
My formula for calculating the number of F's: =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=0")) - SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=59.99"))
This formula will count the number of students who scored higher than 0 (all of them) and subtract the number of students that scored a 60 (I would manually edit this number) or higher. Ideally, instead of "60", I would like the formula to be .60*F3, where F3 is the current possible points for the class.
My formula for calculating the number of D-'s: =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=60")) - SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">=64.99"))
This formula will count the number of students who scored higher than 60 (I would manually edit this number) and subtract the number of students that scored a 65 (I would manually edit this number) or higher. Ideally, instead of "60", I would like the formula to be .60*F3, where F3 is the current possible points for the class, and instead of 64.99, it would be .6499*F3.
This repeats on down the line and works perfectly if there were 100 possible points all the time. But, of course, this is not the case.
I hope this explanation was clear. It's a little difficult for me to explain. Any feedback on how to improve this workbook would be greatly appreciated. Thank you in advance.
