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

    Average-If function needed..



      Average-If function needed..


      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




      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..

          howdy again kirvis,


          remember this thread?



          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..

            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.





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



              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?



              FileMaker, Inc.