
1. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 4, 2010 2:17 PM (in response to Lorette)Try taking the average and standard deviation of the hours field instead of the sub totals.

2. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 8, 2010 10:07 AM (in response to Lorette)Hi,
Taking an average and std dev of the hours field gives the wrong results.
I am looking for the average time it takes to complete each structure, not average time of each line item.
The average of the hours field returns
A  73.3 K  17.5 V  28.3
My expected results are
A: 126.67 hrs = (30 + 300 + 50) / 3
K: 34.5 hrs = (18.5 + 50.5) / 2
V: 42.5 hrs = (35 + 50) / 2
Please help. Thanks.

3. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 5, 2010 11:55 AM (in response to Lorette)Once you have the summary fields so defined, you can use get Summary in a calculation or place the summary field in a sub summary part to get average and STDev for each structure.

4. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 5, 2010 1:34 PM (in response to Lorette)It is still not working...
Below are the expected results (Average)
A: 146.67 hrs = (30 + 300 + 50) / 3
K: 34.5 hrs = (18.5 + 50.5) / 2
V: 42.5 hrs = (35 + 50) / 2
In reality I'm getting the following (averages):
A: 146.67 hrs, K: 34.5 hrs (this is good!)
V: 40.00 hrs < This is very concerning  How did FileMaker come up with that solution
Below are the formulas I used:
sTotalHours = Summary Field = Total of Hours
cTotalHoursStructureID = Calculation Field = GetSummary (sTotalHours / StructureID)
AvgHours = Summary Field = Average of cTotalHoursStructureID
Please let me know what I'm doing wrong. Thanks.

5. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 5, 2010 2:48 PM (in response to Lorette)sAvgHours should be average of Hours.
cAvgHoursStructureID should be GetSummary ( AvgHours ; StructureID )
Not sure why all your posts use / which stands for division instead of ;.

6. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 7, 2010 1:54 PM (in response to Lorette)It still isn't working. Is there a way I can send you screenshots?

7. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 7, 2010 2:09 PM (in response to Lorette)I researched and saw your instructions about entering screenshots.
I created a photobucket account. I have included the table design, report design and results. You will be able to see how the average and standard deviation are wrong. Please let me know how to fix the issue.
http://s1184.photobucket.com/albums/z329/lpurschkevalard/Valard_FileMaker/
Thanks,
Lorette

8. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 7, 2010 3:24 PM (in response to Lorette)I see both what appear to be correct and incorrect field definitions for computing the average hours.
Two different summary fields compute as the "Average of Hours_ActivityStructure" I can't tell from your screen shots if either of these were placed on your layout and like any summary field, they should be placed in a sub summary part and sorted correctly if you want to see the average for that sub group not the average of all the records.
I can't find any standard deviation summary field defined as the "standard Deviation of HoursActivityStructure" and this is what you would need to compute the standard deviation.
Here is a demo file that you can compare to yours. It has the same data as your original post and computes both standard deviation and averages for all three structure ID's. The first layout shows the raw data. The second displays the summary fields.

9. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 8, 2010 8:19 AM (in response to Lorette)Thanks for the demo file.
I added one table and one report and replicated my problem in this file. The average and standard deviation calculations are wrong.
I need to determine the average hours per structure for each structure type.
I created a new table "NewTable_StdDevAvgTest" with all the fields described above. I also created a new report "New_SummaryReportTest" based off the new table (sorted by structure type and structure id). The minimum and maximums are correct , but averages and standard deviations are wrong.
Please download the file here.....
http://www.4shared.com/file/oiGEedAR/StdDevAvgTest_v2.html
Thank you for your all assistance, I`m looking forward to your advice :)

10. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 8, 2010 8:32 AM (in response to Lorette)Here is the corrected copy of your file: http://www.4shared.com/file/YabBKkzJ/StdDevAvgTest_v2.html
All I did was swap your incorrectly defined summary fields for the correctly defined summary fields that were already defined in your table. I replaced NsAVG_Wrong with sAvg and NsSTDv_Wrong with sSTD.
I've now said this several times over: The summary fields must compute the average of and standard deviation of the original Hours field, not the calculation fields that compute the total hours. Both of these fields need to be able to count the individual values as well as summing either the values (average) or the squares of the values (standard deviation) in order to compute the correct results.

11. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 8, 2010 10:05 AM (in response to Lorette)Thank you for your quick response and patience.
I require the following results for the average structure type calculation. I need the average time it takes to complete a structure within each different structure type category. Each structure type is completely different  some strutures types may take a week to complete, some may take a month. I need the total time it takes to complete the structure, then compare it again every other structure in that structure type (category).
The calculation I require is the following:
A Average = 126.67 hrs = (30 + 300 + 50) / 3 {A22 = 30, A23 = 300, A24=50}
K Average = 34.5 hrs = (18.5 + 50.5) / 2 {K1 = 18.5, K2=50.5}
V Average = 42.5 hrs = (35 + 50) / 2 {V3 = 35, V5=50}
Unfortunately, the average of the original hours field calculates the following results
A = 76 = (10 + 20 + 100 + 200 + 50) / 5
K = 17.25, V = 28.3
Is FileMaker capable of calculating my results (126.6, 34.5 & 42.5)? If so, what is the method to use?
Thank you very much for assistance.

12. Re: Please help  Average & Standard Deviation is not working
philmodjunk Oct 8, 2010 9:47 AM (in response to Lorette)Apologies for the bum steers. It took me a bit to see the problem.
You have 5 values for A and so with the fields as defined, FileMaker adds the values and divides by 5 (the number of records) to get an average. While standard deviation uses a much more complex, it also uses the number of records physically present as part of the calculation. Hence, straight summary fields can't compute the values you want.
Short of using a related table where you have one record for each StructureID and Structure Type, you'll need to roll your own summary calculations here.
Average is doable: GetSummary ( sTotalHours ; StructureType ) / GetSummary ( nTotalStructures ; StructureType )
Standard deviation is much more complicated.
Define a calculation field for x squared: cXSquared as cTotalHoursStructureID ^ 2 / GetSummary ( sCount ; StructureID)
Define a summary field to total the squares as sXsquared: Total of cXsquared.
Now you can define a standard deviation calculation as:
Let ( n = GetSummary ( nTotalStructures ; StructureType ) ;
GetSummary ( sXsquared ; StructureType ) / ( n 1 ) 
GetSummary ( sTotalHours ; StructureType ) ^ 2 / ( n * ( n  1 ) )
) ^ .5 
13. Re: Please help  Average & Standard Deviation is not working
Lorette Oct 8, 2010 10:04 AM (in response to Lorette)Thank you very much. This seems to be working!