
1. Re: Best way to calculate and average a list of X*Y items.
philmodjunk Apr 21, 2011 11:52 AM (in response to stevenkw)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 Apr 21, 2011 12:01 PM (in response to 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*41+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 Apr 21, 2011 12:13 PM (in response to stevenkw)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 Apr 21, 2011 12:18 PM (in response to stevenkw)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 Apr 21, 2011 12:19 PM (in response to 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 Apr 21, 2011 12:25 PM (in response to 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*41+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 Apr 21, 2011 12:36 PM (in response to stevenkw)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 Apr 21, 2011 5:14 PM (in response to 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 Apr 21, 2011 5:16 PM (in response to stevenkw)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.