4 Replies Latest reply on Nov 16, 2009 11:04 AM by imgaryshap

    How to exclude certain records from an average

    imgaryshap

      Title

      How to exclude certain records from an average

      Post

      Hello,


      I'm a Filemaker Pro 8 - MAC user.

       

      For one of my reports, I use three fields.   Time A, Time B, and Time C

       

      for some records -any given one of these fields can be left blank.

       

      I want to create an average field in a summary (i.e., Average Time A, Average Time B, and Average Time C).

       

      However, I want the average to show in my leading summary, and only be based on fields that contain data.  I do not want empty fields to be included in the average, for obvious reasons (it distorts the data).

       

      any suggestions?

       

      Thanks so much.

       

      -Gary 

        • 1. Re: How to exclude certain records from an average
          ninja
            

          Howdy gary,

           

          While there are many ways to do this (filtering, looping, etc.) it may be pretty simple to just define fields Anull, Bnull, Cnull and have them be calculated as:

           

          If (TimeA = 0 ; "" ; TimeA )  etc

          Then average the Anull, Bnull, Cnull instead of the TimeA, TImeB and TimeC in your summary field.  With the blanks instead of zeros, you should get what you're after.

           

          Does this fit your needs?

          • 2. Re: How to exclude certain records from an average
            philmodjunk
              

            I believe the Average() function will ignore empty fields.

             

            Average ( field1; Field2; Field3) with 1, 2 and <empty> will return 1.5, with 1, 2 and 0 will return 1.

            • 3. Re: How to exclude certain records from an average
              ninja
                

              PhilModJunk wrote:

              I believe the Average() function will ignore empty fields.

               

              Average ( field1; Field2; Field3) with 1, 2 and <empty> will return 1.5, with 1, 2 and 0 will return 1.


               

              Phil, you are correct.  But summaries apparently work the same way on averaging.

               

              I mocked this up and used a summary field (set to average), then blanked some of the referenced fields to see the behavior...the blank fields weren't included in the summary average either.  As I blanked values, the summary shifted to include only the fields that had non-blank values.

               

              Thus I figured that the real issue wasn't that they were blank, but that they were zero...my earlier post was geared at getting the zeroes blanked out.


              • 4. Re: How to exclude certain records from an average
                imgaryshap
                  

                Thanks so much, guys.

                 

                I will try these suggestions and let you know what works...