3 Replies Latest reply on Jan 21, 2010 11:37 AM by TSGal

    Average-If function needed..

    kirvis

      Title

      Average-If function needed..

      Post

      Hi all,

       

      I am currently using a wonderful custom function called TypeSumField. I found it here. In short, it makes it possible to sum a certain field from records that meet a certain condition. (i.e. if category = "Bread and pastries" then Sum( amount ) ) 

       

      The source code for this function is:

       

       

      //This function will sum all SumFields whose TypeField = Type

      //Start should =0 or blank

       

      Let([

       

      End = Count(TypeField);

       

      counter = Start +1;

       

      total =  If(GetNthRecord(TypeField;counter)=Type;  GetNthRecord(SumField;counter) ; 0 )

       

       

      ];

       

       

      Case(counter<End ;  total + TypeSumField(TypeField;Type;SumField;counter) ; total

       

      ))

       

       

      What I need now is a comparable function, but then with average. So, that it calculates the average of the field "amount" of all records where the field "category" has the value "Bread and pastries".

       

      Is it possible to do this with a custom function that is comparable to the TypeSumField custom function?

       

        • 1. Re: Average-If function needed..
          ninja
            

          howdy again kirvis,

           

          remember this thread?

          http://fm.lithium.com/fm/board/message?board.id=FM-en-4&message.id=31824#M31824

           

          While I'm sure that one of the folks on the board can come up with a custom function (and that it may be your preferred method based on your post) the approach I outlined in the other thread would be amenable to averaging via summary field.

           

          Food (pastries apparently) for thought...

          • 2. Re: Average-If function needed..
            kirvis
              

            Hi Ninja,

             

            Yes, I do remember your previous post on this similar question I posted earlier.

             

            I however prefer a custom function, to prevent the system from becoming too extensive as it already is quite large. The TypeSumField function works like a charm for me, and prevents me from having to add a dozen or so additional fields with the if-function in the calculation as you suggest.

             

            It is however an option if nobody else here can come up with a custom function that does the trick at once. Thanks again for your reply.

             

            Cheers,

             

            Kirvis 

            • 3. Re: Average-If function needed..
              TSGal

              kirvis:

               

              Thank you for your posts.

               

              If the first calculation gives you a total, then an average would be the total divided by the number of occurrences.   Wouldn't that be Total/End?  Or, is this a weighted average?

               

              TSGal

              FileMaker, Inc.