### Title

Please help - Average & Standard Deviation is not working

### 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)

** **

**Any help you can provide would be greatly appreciated. Thanks!**

** **

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

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