1 Reply Latest reply on Jun 27, 2012 11:52 AM by philmodjunk

    Select highest numbers and divide by 2

    benitosalinas

      Title

      Select highest numbers and divide by 2

      Post

       I have 5 different number fields.  I need to select the two fields with the highest two numbers, add those two highest numbers and divide the sum by 2 to get an average.  I need that average to go to a different emply field. Please help my knowledge of FM is limited.

      Please help

       I did not mention that each one of the five numbers is the sum of 4 numbers in different fields.  I need to add the two highest sums and divide them by two.  Sorry I was not clear on this.  Can you help me?

        • 1. Re: Select highest numbers and divide by 2
          philmodjunk

          It's actually easier to access the "largest two numbers" if you use a related table of  records  for these values instead of 5 individual fields. In such case, the relationship can specify a sort order such that the first two related records are the largest and then the GetNthRecord function can correctly access the largest two values in order to perform this calculation:

          ( getNthRecord(RelatedTable::NumberField ; 1 ) + getNthRecord ( RelatedTable::NumberFifeld ; 2 ) ) / 2