7 Replies Latest reply on Mar 8, 2015 11:03 AM by JuanChamorro

    I get the wrong average result



      I get the wrong average result



      I am an ESL teacher and I use FM to present the students with their term tests results but I can't get the final average result that I am supposed to.

      I have number fields with the test results. These are in the body of the layout. Each test result has a skill field attached such as writing, reading, grammar... etc.

      Then I have a sub-summary part for each skill with a summary field to get the average test results of each skill.

      Finally I have another sub-summary part for each student. I want to get the skills average results of each student as distinct from the total test average result but I can't. I can only get the average test result and I can't get the average skill result.

      Some students have pointed out to me that those to results are different. And since the average skill result has a higher value on their calculators they want me to use that calculation.

      I have also tried with the GETSUMMARY fuction but I can't get to the result you get when do calculate the average of the skill results.

      What am I doing wrong? Can you point me to the right direction?



        • 1. Re: I get the wrong average result

          Is it possible to post a sample file with some sample data. In all likelihood, I think you may have complicated this more then needed, and you may have the calculations on the wrong table/table occurrence.

          • 2. Re: I get the wrong average result

            Can you confirm that each test result is a separate record?

            And is this the average that you want to calculate?

            (Skill 1 Average + Skill 2 average + .... ) / number of "average" values

            GetSummary is a step in the right direction, but because it returns the same value for every test result record, your summary field that references this calculation comes out wrong as you get one such "average" for every test result instead of one such value for every skill tested.

            One solution is to "sum the reciprocal". A a "count of" summary field that just "counts" a field in your table that is never empty: sCount.

            Then set up your getSummary calculation to be: GetSummary ( Average ; Skill ) / GetSummary ( Count ; Skill ) // I am calling the "sorted by" field used in your sub summary part "Skill".

            Now you should be able to set up a summary field to use this adjusted value to get an accurate average.

            PS. And congratulate your students for keeping you "on your toes" when it comes to calculating their grades. As someone who once taught high school and (for one disastrous year) middle school, I can appreciate that attitude as being a very positive one compared to those that seem not to care what their grade is.

            PS. another way to get these values if using FileMaker 12 or newer is to use the executeSQL function instead of the method outlined here.

            Caulkins Consulting, Home of Adventures In FileMaking

            • 3. Re: I get the wrong average result

              Thank you Steve,

              Since I don't know how to post the actual FM file, I upload a screen capture. I hope that'll be useful to you. Otherwise, could you tell me how to post the file, please?


              • 4. Re: I get the wrong average result

                Thank you PhilModJunk,

                Yes, I have one separate record for each test result.

                Yes, that is the kind of average I'd like to do: (Skill 1 Average + Skill 2 average + .... ) / number of "average" values

                I have to read your replay several times until I understand it completely, but I think I get part of the idea. I'm going to experiment with a Count Summary field together with the getSummmary one.

                I like what you say about congratulating the students for keeping me on my toes. Yes, I can see you have been a teacher!!

                Thanks a lot,



                • 5. Re: I get the wrong average result

                  You cannot upload database files to this forum, only image files. but you can upload to a file sharing site such as Drop Box and then post the down load link to your file here via a comment in Post A New Answer.

                  • 6. Re: I get the wrong average result

                    Steve and PhilModJunk,

                    This is a like to my FM file. I'd be very grateful if you could take a look and tell me what you think about it and how to overcome my problem

                    Thanks a lot,



                    • 7. Re: I get the wrong average result

                      I am sorry PhilModJunk but I don't seem to be able to get the expected grade average result.

                      I have tried with a summary count field together with the getSummary field that you suggested but I must keep doing something wrong because I get a very small value.

                      Can you explain it to me again , please?

                      Thank you for your patience,