3 Replies Latest reply on Dec 18, 2013 9:56 AM by philmodjunk

    newbie needs help with average calculation

    ChrisFitzsimons

      Title

      newbie needs help with average calculation

      Post

           Hi there,

           sorry to annoy anyone with this question, but I do need help.

           I have a school database for students reports. Each student has a unique ID number. In the reports table, the student has a new record for each subject they are doing (eg 1 record for Mathematics, 1 for Science etc, all with the common ID Number). For each subject the teacher allocates grades for different outcomes (eg 5 outcomes for Maths, such as A, A, B, D, C). I have a calculation that converts those grades to numbers (A = 5, B = 4 etc) and then averages those numbers and converts the average to an Overall Grade (eg a students' Overall Grade for Mathematics is a B).

           End result is that a student doing 9 subjects has 9 Overall Grades.

           What I need help with is averaging (or even summarising?) these Overall Grades for something like a GPA. Looking at other posts online here, I think the sticking point I have is that the data is all in the one table (and will remain that way!) and I need it based on the ID Number.

           Please please please help!

           Thanks

           Chris

      Overall_Grade.JPG

        • 1. Re: newbie needs help with average calculation
          philmodjunk
               

                    I think the sticking point I have is that the data is all in the one table (and will remain that way!)

               That does make this data much harder to work with. Related tables are an available feature in FileMaker for a reason--to make things easier when this type of situations arises.  wink

               But that's not exactly what I see here. I see one record for each subject. I don't see the individual grades in your screen shot so I don't know how you've set up that detail. Since you apparently did not use a related table for the individual grades for each subject, I don't know if you used a repeating field for the individual grades or a set of individiual fields in the record.

               The basic calculation for computing an average is the same, but the syntax is different.

               Repeating field of individual grades:

               Sum ( repeatingField ) / Count (repeatingField )

               Individual fields:

               Sum ( Field1 ; field2 ; Field3 ; field4 ) / Count ( Field1 ; Field2 ; Field3 ; Field4 )

               Note: Count counts fields that are not empty. Thus, you will get a different average when a field is left blank than if a zero is entered into that field.

          • 2. Re: newbie needs help with average calculation
            ChrisFitzsimons

                 Hi there

                 thanks for your reply. I've attached another screen shot that shows the different outcomes as different fields (i.e. not a repeating field)

                  

                 Your suggestion:

            Individual fields:

            Sum ( Field1 ; field2 ; Field3 ; field4 ) / Count ( Field1 ; Field2 ; Field3 ; Field4 )

                 seems to me that it would work well in calculating the original Overall Grade for a subject (and is very similar to my calculation). But what I need is the average of the student's Overall Grades - i.e. it is one field ("Overall Grade"), but from about 8 or 9 records (that student's subjects)

                 hope this helps?

            • 3. Re: newbie needs help with average calculation
              philmodjunk

                   The same aggregate functions work for related records.

                   You can set up a self join by StudentID and then

                   Average (Students|SameID::OverallGrade )

                   Can produce the desired average.

                   You can also, in many layout designs, set up an Average summary field as the average of the overall grade. If you use a list view layout, you can put that summary field in a sub summary layout part "When sorted by student ID" to get the average of the overall grades for each student. This requires sorting your records by StudentID or whatever "sorted by" field you specify for the sub summary layout part or the sub summary layout part will not be visible.