9 Replies Latest reply on Apr 21, 2011 5:16 PM by philmodjunk

    Best way to calculate and average a list of X*Y items.

    stevenkw

      Title

      Best way to calculate and average a list of X*Y items.

      Post

      Hello All,

      I was wondering if there is a simple and easy way to calculate and average a list of X*Y numbers.

      I currently do this with a (not so pretty) looping script. The data is entered like this:

      1*2.18

      1*2.33

      7*1.18

      6*1.38

      1*1.70

      2*.36

      1*.66

      2*.54

      15*.54

       

      And the script multiplies and sums the results, sums the number of items on the left of the *, then divides the result by the number of items giving the average.

       

      33.31 / 36 = .9252777777777778

       

       

      I would like to do this without a script and would like the entry to be less cumbersome.

       

      I was thinking of creating a field where the user could enter the data in 1 line. as in:

      1*2.18+1*2.33+7*1.18+6*1.38+1*1.70+2*.36+1*.66+2*.54+15*.54

       

      Then some how calculate the total\number of items.

       

      Any thoughts on this?

       

      Thank you,

      --StevenKW

       

      Screen_shot_2011-04-21_at_10.43.29_AM.jpg

        • 1. Re: Best way to calculate and average a list of X*Y items.
          philmodjunk

          Evaluate can take text and evaluate text in a field like it was an expression typed into a specify calculation dialog box.

          If you wanted, you could define a field such as: Evaluate ( XYField ) and then you can define a summary field that computes the average of this new calculation field.

          However, I don't see the need (you may have a very good reason for this, it's just not here for me to see), for using a single field to record both values if the expression is always going to be X*Y.

          Define two fields: X, and Y. Define a calculation field, cProduct as X * Y and then your summary field can compute the average of cProduct.

          • 2. Re: Best way to calculate and average a list of X*Y items.
            stevenkw

            There is an unknown number of X * Y pairs. The evaluate function is what got me started on this line of thinking in the first place. The problem is evaluate alone wont count how many items there are in order to get an average of all items.

            These are actually length measurements and I want to know the average of all the items combined.

            1*2
            2*3
            3*4

            1+2+3 = 6 items

            2+3+3+4+4+4 = 20 units of length

            20/6 = 3.33 average unit length.

            • 3. Re: Best way to calculate and average a list of X*Y items.
              philmodjunk

              Each pair can be entered into a separate record. Create as many records as you need to enter all your pairs. Enter X values into the X field and Y values into the Y field. Then use the calculation and summary fields I suggested to compute the average of the products.

              If you want to use evaluate, you can use the first method I suggested, but I don't see a reason for doing so.

              Edit note: had an idea for computing it all in one field, but it was incorrect...

              • 4. Re: Best way to calculate and average a list of X*Y items.
                philmodjunk

                Ok, took another crack at computing the average of the products from an expression in this format entered into a field named ExpressionField:

                X1 * Y1 + X2 * Y2...

                Let ( [ S = Evaluate ( ExpressionField ) ; pairs = Length ( Filter ( ExpressionField ; "*" ) ) ] ; S / pairs )

                (Turns out my original idea works after all...)

                • 5. Re: Best way to calculate and average a list of X*Y items.
                  stevenkw

                  I have hundreds of these measurements and I'd like to keep them all in one record. It works that way currently but I have to use a script to make it happen. I also want the numbers to be easy to enter.

                  • 6. Re: Best way to calculate and average a list of X*Y items.
                    stevenkw

                    I am not dividing by the number of pairs. i am dividing by the sum total of items in each pair. 

                    1*2
                    2*3
                    3*4

                    1+2+3 = 6 items

                    2+3+3+4+4+4 = 20 units of length

                    20/6 = 3.33 average unit length.

                    3 pairs 1+2+3= 6 items

                    Thank you,

                    --StevenKW

                    • 7. Re: Best way to calculate and average a list of X*Y items.
                      philmodjunk

                      I have hundreds of these measurements and I'd like to keep them all in one record.

                      Why? It's pretty easy to put such values into a related table and use a portal to enter the measurement pairs. It offers a lot of options for working with your data that you give up if you keep it all in one record. The more measurements you have, the more difficult it will be to work with the data all in one record.

                      Sorry about missing the fact that you are dividing by the total measurement count, not the pairs.

                      Using the original, separate record for each pair method, you can still compute the result you want, but can't use an Average summary field. You can define a total of summary field, a count of summary field and then put this expression into a calculation field to give you the result you want:

                      TotalSummaryField / ( CountSummaryField * 2 )

                      With a related table, your parent record can compute the same result with Sum and Count aggregate functions.

                      To modify my other calc, you again would just multiply the number of pairs by two before dividing.

                      Let ( [ S = Evaluate ( ExpressionField ) ; pairs = Length ( Filter ( ExpressionField ; "*" ) ) ] ; S / ( pairs * 2 ) )

                      • 8. Re: Best way to calculate and average a list of X*Y items.
                        stevenkw

                        I'll give it a try. I just hadn't thought about doing it that way and the current system dosen't work that way. 

                        I thought that there would be some urber geeky way to parse the entry. I was thinking of storing the results into repeating fields behind the sceens and then spitting out an answer.

                        Thanks for the responses.

                        --StevenKW

                        • 9. Re: Best way to calculate and average a list of X*Y items.
                          philmodjunk

                          A table of related records works much better than a repeating field (which could be done, BTW). Repeating fields are what we used before we could set up related tables in FileMaker. They have occaisional uses, but have been replaced by using a related table for most situtations.