FileMaker Pro and FileMaker II provide a StDev function that allows you to calculate the standard deviation of an entire population represented by values in a repeating field. There is also a summary field option to calculate this result over a range of records. You cannot use these built-in functions, however, if you need to calculate the standard deviation of a sample. Certain professions require that the standard deviation of a sample be used, rather than the standard deviation of a population. To find the correct deviation of a sample, you must calculate the value the "long" way:
Suppose you want to find the sample standard deviation given Measurements, a repeating field containing the values 90, 85, 87, 50, 92, 95, and 100.
1. Define a new calculation field, D, with the same number of repetitions as Measurements, and a number result. Use the formula:
( Measurements - Average ( Measurements) ) ^ 2
2. Define another calculation field, StDev.sample, with a Number result. Use the formula:
( Sum ( D ) / (Count ( Measurements ) -1) ) ^ .5
The result of StDev.sample will be 16.46.
----------------------------------------------------
And, to further complicate matters:
FileMaker Pro and ClarisWorks give two different results for Standard Deviation. This is because FileMaker Pro uses the formula for Standard Deviation based upon a population, and ClarisWorks uses the formula for Standard Deviation based upon a sample.
To determine the variance (based upon population) of a group of numbers, perform the following steps:
1) find the average (mean) of a group of numbers. That is, add up all the values of the numbers and divide by the number of entries.
For the series of numbers 115, 121, 130, and 156, the mean is:
(115 + 121 + 130 + 156) / 4
(522) / 4
130.5
2) subtract the mean from each of the entries to yield the deviations;
115 15.5
121 9.5
130 0.5
156 -25.5
3) square the deviations
115 15.5 240.25
121 9.5 90.25
130 0.5 0.25
156 25.5 650.25
4) add the square of the deviations
240.25 + 90.25 + 0.25 + 650.25 = 981
5) divide the sum of the deviations squared by the number of entries yields the variance.
981 / 4 = 245.25
6) the square root of the variance is the standard deviation
245.25 ^ 0.5 = 15.6604597633658
The result from step 6 is the same as FileMaker Pro using either StDev() function within a calculation field Standard Deviation within a summary field, whichever is applicable.
To obtain the standard deviation based upon a sample, change step 5 to:
5) divide the sum of the deviations squared by the number of entries minus one. That is:
981 / (4 - 1)
981 / 3
327
The same formula for step 6 is used. That is, take the square root of the variance.
327 ^ .5 = 18.0831413200251244
The results in steps 5 and 6 correspond to the VAR() and STDEV() functions, respectively, using ClarisWorks.