3 Replies Latest reply on Oct 29, 2010 4:40 PM by philmodjunk

    Median and mode for a set of records?

    GeorgeJetson

      Title

      Median and mode for a set of records?

      Post

      I have a survey database with Person table. Each person has an age (calculated from DOB in whole years). I want to find a set of records and determine the modal and median age. Does FileMaker have these functions? I haven't been able to find them.

      Thanks for your help.

        • 1. Re: Median and mode for a set of records?
          philmodjunk

          No, but you can script for them.

          Go To Record [Ceiling ( Get ( FoundCount ) / 2 ) ]

          will land you on the median after you've sorted them in increasing order by age. (and you can script to find the midpoint of the two center values if you have an even number of records if you need that level of precision.)

          Mode is a bit trickier:

          You can define a "count of" summary field to count the number of records with each age group by using a calculation field that uses:

          GetSummary ( sCountSummaryField ; AgeField )

          Then it's a matter of stepping through your found set in a script to identify the group with the largest count.

           

          • 2. Re: Median and mode for a set of records?
            GeorgeJetson

            Thanks for the clues. Sorry I have been busy to return.

            So what you are saying is to run a script and then store the result somewhere? Maybe in a global field?

            As you say, mode is a bit tricky but I will give it a go.

            Thanks again.

            • 3. Re: Median and mode for a set of records?
              philmodjunk

              I've figured out a way for this to work without scripting. The records storing the values must all be present in the found set and be sorted by the field on which you want to compute the median and mode. If there are two or more modes, the calculation returns the largest mode.

              It took several layers of summary and calculation fields to get the mode, but it works.

              http://www.4shared.com/file/v4JBh76R/MedianModeDemo.html