13 Replies Latest reply on Oct 8, 2010 10:04 AM by Lorette

### Post

Hi,

I have the minimum, maximum and total working but the average and standard deviation is not working when I use the same method.

Below are the formulas

sTotalHours = Summary Field = Total of Hours

sTotalCountA = Summary = Count of StructureID

CFraction_B = CAlculation = 1 / GetSummary (sTotalCountA;STructureID)

Total Structures = Summary Field = Total of CFraction_B (works)

cTotalHoursStructureID = Calculat = GetSummary (sTotalHours; StructureID)

MinimumHours = Summary Field = Minimum of cTotalHoursStructureID  (Works)

MaxHours = Summary Field = Max of cTotalHoursStructureID   (Works)

AvgHours = Summary Field = Average of cTotalHoursStructureID   (Doesn't Work)

StandDevHours = Summary Field = Stand Deviation of cTotalHoursStructureID (Doesn't Work)

Test Data

Structure Type,  STructure Number,  Hours

A, 22, 10 hrs

A, 22, 20 hrs

A, 23, 100 hrs

A, 23, 200 hrs

A, 24, 50 hrs

K, 1, 8.5 hrs

K, 1, 10 hrs

K, 2, 20.5 hrs

K, 2, 30 hrs

V, 3, 15 Hrs

V, 3, 20 Hrs

V, 5, 50 Hrs

Expected Results (By Structure Type)

A

Total Structures = 3  Total Hours = 440

Minimum = 30  Maximum = 300

Average = 126.67    Standard Deviation = 150.44

(Average & Standard Deviation formulas don't work)

K

Total Structures = 2 Total Hours = 69

Minimum = 18.5    Maximum = 50.5

Average =  34.5    Standard Deviation = 22.6

(Average & Standard Deviation formulas don't work)

V

Total Structures =2  Total Hours = 85

Minimum = 35   Maximum = 50

Average =  42.5     Standard Deviation = 10.6

(Average & Standard Deviation formulas don't work)

** Oct 8 , corrected get summary from "/" to ";"

Try taking the average and standard deviation of the hours field instead of the sub totals.

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

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.

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.

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

It still isn't working. Is there a way I can send you 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

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.

http://www.4shared.com/file/-4RMyECG/StdDevAvgTest.html

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.

http://www.4shared.com/file/oiGEedAR/StdDevAvgTest_v2.html

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.

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.

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 do-able:  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