6 Replies Latest reply on May 27, 2011 2:17 AM by DavidWinegar

    How to return the Mode of a list of numbers

    DavidWinegar

      Title

      How to return the Mode of a list of numbers

      Post

      Can anyone help me on this...trying to figure out how to return the MODE of a list of numbers in Filemaker Pro 11.  I thought that this would be a common and simply thing, but so far I cannot figure out how to do it.

      Anyone give me advice on how to do this?

      Thanks in advance!

        • 1. Re: How to return the Mode of a list of numbers
          LaRetta_1

          Questions:

          How do we access this list of numbers? Is it a string in a single text field separated by comma?  Is it a multi-line list in single field separated by carriage returns?  Or is it records in the table?  Or records in a related table? 

          Mode is simply counting the number of occurrences and returning the number which appears most frequent.  In case of tie, do you both all of the ties listed and if so, how should the result be displayed? "Number 15 returned 11 times", only number "15", or if tie "15, 97"?

          • 2. Re: How to return the Mode of a list of numbers
            DavidWinegar

            Hi thanks for your response and help--I should have been more specific but I will clarify here now.

            The values will be collected into records in a table - they are responses from a questionaire.  I then need to find the one that occurs the most often--if it is a tie, I need both values to be returned but I do not need a count of each one.

            Hope this helps!

            Thanks again for your help--it's much appreciated.

            • 3. Re: How to return the Mode of a list of numbers
              LaRetta_1

              You haven’t said where you need this number displayed.  Should it be determined by each parent record (each question) so it displays on the parent table?  I will assume so.  And in case of tie if I understand, the value would be the same in this case, right?  So try this and let me know if I’m missing the mark:

              Go to your graph and select your child table (responses).  Click ++ to create another occurrence of it (it will name itself Responses 2).  Join it to your Responses table as:

              ParentID = ParentID
              AND
              number = number

              Then in your child table, create a calculation (number) called cBestResponse or something as:  Count ( Responses 2::number )

              Go back to your graph and select the Responses = Responses 2 relationship.  Below for options on sorting, set sorting to descending value on cBestResponse in Responses 2.  Now place Responses 2::number directly onto your parent layout.  This is the best response. 

              If I'm missing the mark, let me know. 

              • 4. Re: How to return the Mode of a list of numbers
                DavidWinegar

                OK well tried to follow what you suggest but I am an admitted noob at this and am a bit lost.  I made the two tables and linked them and am trying with three test fields named number1, number 2 and number 3.  I made the cBestResponse but when I place it to my form and run it I end up with the data 1, 1, 2 a cBestResponse of 3, so it is just returning the number of items (I'm using in my field Count ( Responses 2::Number1;Number2;Number3 ))  and what it should return to me is 1, which is the mode of 1,1,2

                What am I missing?  Any way you could upload a simple example for me of inputing 3 numbers and having it tell me the mode?

                Sorry for being so dense, filemaker is brand new to me.

                • 5. Re: How to return the Mode of a list of numbers
                  LaRetta_1

                  "I made the two tables and linked them and am trying  with three test fields named number1, number 2 and number 3."

                  Oh, no, you should not use fields but rather records which is what I asked originally.  You haven't even built this solution yet?  Then you need to step back and determine the proper structure required before worrying about how to provide the Mode.

                  Anyway, here is how I would design it, since you really have nothing yet.

                  http://www.4shared.com/file/ofpS0ife/questions.html

                  • 6. Re: How to return the Mode of a list of numbers
                    DavidWinegar

                    THANKS LaRetta!!  You are right, I was a bitpre-occupied with just testing to see if the Mode can be found and didn't think out quite clearly the whole thing.  You example helps tons so I thank you very much for doing that for me. 

                    David