4 Replies Latest reply on Apr 1, 2013 1:08 PM by LarsLarsson

    Grading system for teachers

    LarsLarsson

      Title

      Grading system for teachers

      Post

            

           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 Filemaker pro 11 to calculate the final grades.

            

           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 Filemaker 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 doing this in Excel:

           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 excel 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

           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 i excel: 

           https://www.dropbox.com/s/6i494lrs4mf5smg/TEST1A.xlsm

           Here is a pdf of what I'd like Filemaker to help me out with: https://www.dropbox.com/s/qpr3nucad3stpch/example.pdf

            

            

        • 1. Re: Grading system for teachers
          philmodjunk

               Why do any of this in Excel?

               This all looks like things that you can do directly in FileMaker, but with a significantly different design to your tables and relationships.

          • 2. Re: Grading system for teachers
            LarsLarsson

                 I'd like to do it in Filemaker but haven't a clue how to...

                 Do you?

            • 3. Re: Grading system for teachers
              philmodjunk

                   What version of FileMaker are you using?

                   As I said this can all be done in FileMaker. But you'll need to start with a reasonable data model. A data model is the set of field, table and relationship definitions that you create in Manage | Database.

                   I can't spell all of that out in detail as some of that will depend on exactly how you need to work with this data.

                   To start, you'll need at least two related tables, likely more.

                   Students----<Criteria

                   Students::__pkStudentID = Criteria::_fkStudentID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   This allows you to record multiple student scores (what you called "criteria"?) in the Criteria table. With this relationship, the number of such scores for each student can be as flexible as needed and yet you can still compute a Grade from the recorded criteria.

                   Given the complex algorithm that requires counting the number of letter grades of each type, this will be more easily done via a use of ExecuteSQL, but that option is only available in FileMaker 12. If you are using an older version of FileMaker, this grade can still be calculated but the method used will be more complex--involving multiple Tutorial: What are Table Occurrences? that refer to Criteria, but match only to a specific letter grade for a specific student id value.

              • 4. Re: Grading system for teachers
                LarsLarsson

                     Thanks alot for taking the time to explain! I'll digg into it.