Have a question if someone would be nice to help me:
I am a teacher.
Swedish grading system (secondary upper grade) uses the grades A-F.
To get the grade A on a course (a course that has for example 5 criterias to live up to), the course's ALL 5 criterias have to be full filled up to level A. Only then is the final grade A.
In a course that have 5 criterias to live up to I have a student who has lived up to let's say A, C, A, C and C on the 5 criterias.
I want excel to calculate the final grade.
The grading system works like this: to get an A in a course, ALL 5 criterias must have reached A.
To get the grade B however, a MAJORITY of the criterias of A must have been reached (in this case 3 A's)
The student must then of course also have full filled all E (5) and all C (5 of them as well).
So if I in the first column in a spread sheet on the first row have the first criteria, second column I have the 2nd criteria (and so forth) and in row 2 puts A (for grade A) under first criteria, C (the grade C) under the 2nd criteria and so forth –*how do I then in the end column get excel to calculate how many A's the student have reached? And how many C's and E's?
I would also like excel to give me a nice diagram that shows graphically (with bars or something like that) that for example this student (in the example above) has reached the final grade C (which is the total in this case as the student have met all criterias of E, all criterias of C but not a majority of the A criterias (which if he had would have resulted in the final grade B.
Got this code from someone:
Function GetGrade(GradeRange As Range) As String
GradeCount = GradeRange.Count
CountA = WorksheetFunction.CountIf(GradeRange, "A")
CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD
If CountA = GradeCount Then
GetGrade = "A": Exit Function
ElseIf CountA > GradeCount / 2 Then
GetGrade = "B": Exit Function
ElseIf CountB = GradeCount Then
GetGrade = "B": Exit Function
ElseIf CountB > GradeCount / 2 Then
GetGrade = "C": Exit Function
ElseIf CountC = GradeCount Then
GetGrade = "C": Exit Function
ElseIf CountC > GradeCount / 2 Then
GetGrade = "D": Exit Function
ElseIf CountD = GradeCount Then
GetGrade = "D": Exit Function
Else
GetGrade = "E": Exit Function
End If
End Function
There are still some things I need to get right;
1) If a student has even only one "F", the final grade has to be F. No matter if there are A's, E's or C's among what the student full filled.
2) If the student (taking a coarse that has 5 criterias to fullfill) has for example 3 A's and 2 E's it means that he or she has not reached C on two criterias. To get final grade "B", ALL C's must have been full filled.
So in this case the final grade has to be E. That is because to get "B" as final grade all C's must be full filled and a majority of A's. So, to be clearer: no matter how many A's you've got, if you have one E you have to lift that up (by studying more!) to a C to be able to get the B as a final grade.
Is it possible to do calculations like that?
Got this from someone:
Function GetGrade(GradeRange As Range) As String
GradeCount = GradeRange.Count
CountA = WorksheetFunction.CountIf(GradeRange, "A")
CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD
CountF = WorksheetFunction.CountIf(GradeRange, "F")
CountBOnly = WorksheetFunction.CountIf(GradeRange, "B")
CountCOnly = WorksheetFunction.CountIf(GradeRange, "C")
CountDOnly = WorksheetFunction.CountIf(GradeRange, "D")
CountEOnly = WorksheetFunction.CountIf(GradeRange, "E")
If CountE = GradeCount Then
MaxGrade = "E"
End If
If CountD = GradeCount Then
MaxGrade = "D"
End If
If CountC = GradeCount Then
MaxGrade = "C"
End If
If CountB = GradeCount Then
MaxGrade = "B"
End If
If CountF > 0 Then
GetGrade = "F"
ElseIf CountA = GradeCount Then
GetGrade = "A": Exit Function
ElseIf CountA > GradeCount / 2 Then
GetGrade = "B"
ElseIf CountB = GradeCount Then
GetGrade = "B": Exit Function
ElseIf CountB > GradeCount / 2 Then
GetGrade = "C"
ElseIf CountC = GradeCount Then
GetGrade = "C": Exit Function
ElseIf CountC > GradeCount / 2 Then
GetGrade = "D": Exit Function
ElseIf CountD = GradeCount Then
GetGrade = "D"
Else
GetGrade = "E": Exit Function
End If
If Asc(MaxGrade) > Asc(GetGrade) Then GetGrade = MaxGrade
End Function
But something is wrong with the code, if I enter F anywhere it says "Value fault" (or something like that, its in swedish). Should result in F.
And 3 A's and 2 C's results in C (not B as it should).
Can anyone help me with this?
Here is the file with the last code above:
https://www.dropbox.com/s/6i494lrs4mf5smg/TEST1A.xlsm
I am a teacher.
Swedish grading system (secondary upper grade) uses the grades A-F.
To get the grade A on a course (a course that has for example 5 criterias to live up to), the course's ALL 5 criterias have to be full filled up to level A. Only then is the final grade A.
In a course that have 5 criterias to live up to I have a student who has lived up to let's say A, C, A, C and C on the 5 criterias.
I want excel to calculate the final grade.
The grading system works like this: to get an A in a course, ALL 5 criterias must have reached A.
To get the grade B however, a MAJORITY of the criterias of A must have been reached (in this case 3 A's)
The student must then of course also have full filled all E (5) and all C (5 of them as well).
So if I in the first column in a spread sheet on the first row have the first criteria, second column I have the 2nd criteria (and so forth) and in row 2 puts A (for grade A) under first criteria, C (the grade C) under the 2nd criteria and so forth –*how do I then in the end column get excel to calculate how many A's the student have reached? And how many C's and E's?
I would also like excel to give me a nice diagram that shows graphically (with bars or something like that) that for example this student (in the example above) has reached the final grade C (which is the total in this case as the student have met all criterias of E, all criterias of C but not a majority of the A criterias (which if he had would have resulted in the final grade B.
Got this code from someone:
Function GetGrade(GradeRange As Range) As String
GradeCount = GradeRange.Count
CountA = WorksheetFunction.CountIf(GradeRange, "A")
CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD
If CountA = GradeCount Then
GetGrade = "A": Exit Function
ElseIf CountA > GradeCount / 2 Then
GetGrade = "B": Exit Function
ElseIf CountB = GradeCount Then
GetGrade = "B": Exit Function
ElseIf CountB > GradeCount / 2 Then
GetGrade = "C": Exit Function
ElseIf CountC = GradeCount Then
GetGrade = "C": Exit Function
ElseIf CountC > GradeCount / 2 Then
GetGrade = "D": Exit Function
ElseIf CountD = GradeCount Then
GetGrade = "D": Exit Function
Else
GetGrade = "E": Exit Function
End If
End Function
There are still some things I need to get right;
1) If a student has even only one "F", the final grade has to be F. No matter if there are A's, E's or C's among what the student full filled.
2) If the student (taking a coarse that has 5 criterias to fullfill) has for example 3 A's and 2 E's it means that he or she has not reached C on two criterias. To get final grade "B", ALL C's must have been full filled.
So in this case the final grade has to be E. That is because to get "B" as final grade all C's must be full filled and a majority of A's. So, to be clearer: no matter how many A's you've got, if you have one E you have to lift that up (by studying more!) to a C to be able to get the B as a final grade.
Is it possible to do calculations like that?
Got this from someone:
Function GetGrade(GradeRange As Range) As String
GradeCount = GradeRange.Count
CountA = WorksheetFunction.CountIf(GradeRange, "A")
CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD
CountF = WorksheetFunction.CountIf(GradeRange, "F")
CountBOnly = WorksheetFunction.CountIf(GradeRange, "B")
CountCOnly = WorksheetFunction.CountIf(GradeRange, "C")
CountDOnly = WorksheetFunction.CountIf(GradeRange, "D")
CountEOnly = WorksheetFunction.CountIf(GradeRange, "E")
If CountE = GradeCount Then
MaxGrade = "E"
End If
If CountD = GradeCount Then
MaxGrade = "D"
End If
If CountC = GradeCount Then
MaxGrade = "C"
End If
If CountB = GradeCount Then
MaxGrade = "B"
End If
If CountF > 0 Then
GetGrade = "F"
ElseIf CountA = GradeCount Then
GetGrade = "A": Exit Function
ElseIf CountA > GradeCount / 2 Then
GetGrade = "B"
ElseIf CountB = GradeCount Then
GetGrade = "B": Exit Function
ElseIf CountB > GradeCount / 2 Then
GetGrade = "C"
ElseIf CountC = GradeCount Then
GetGrade = "C": Exit Function
ElseIf CountC > GradeCount / 2 Then
GetGrade = "D": Exit Function
ElseIf CountD = GradeCount Then
GetGrade = "D"
Else
GetGrade = "E": Exit Function
End If
If Asc(MaxGrade) > Asc(GetGrade) Then GetGrade = MaxGrade
End Function
But something is wrong with the code, if I enter F anywhere it says "Value fault" (or something like that, its in swedish). Should result in F.
And 3 A's and 2 C's results in C (not B as it should).
Can anyone help me with this?
Here is the file with the last code above:
https://www.dropbox.com/s/6i494lrs4mf5smg/TEST1A.xlsm
