ValStdDevS ( ValueList ; ArithMeanValue )

Gives the standard deviation of a sample, where the set of numbers are in a value list

Average rating: 4.1 (20 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

ValStdDevS ( "5¶8¶24¶3¶6" ; ValueMean ( "5¶8¶24¶3¶6" ) )
ValStdDevS ( "5" ; ValueMean ( "5¶8¶24¶3¶6" ) )
ValStdDevS ( "5¶a¶24¶3¶6" ; ValueMean ( "5¶a¶24¶3¶6" ) )

Sample output:

8.467585...
? --> divides by N-1; N=1; so, divide by 0
9.44986... ['a' resolves as 0]

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
ValStdDevS function

Based on Kevin Woods's Standard_Deviation, which is in turn
Based on Standard Deviation - Formula 1 by Richard Bowles 'http://richardbowles.tripod.com/maths/normdist/calc_sd1.htm'
Standard Deviation of a Sample
The standard deviation of a sample has one less degree of freedom, so the sum of squares is divided by N-1, then sqrt

Format
ValStdDevS( ValueList ; ArithMeanValue ) ; 

Parameters
ValueList       - The ValueList containing the values to be calculated.
ArithMeanValue  - The average of the values in the list.

Data type returned
Number

Function is Recursive
a global variable ($$~ValueStdDev) is used, but nulled in the final step
global rather than local variable used only because they're less common, so this function is less likely to wipe out a user's variable

Description
Calculates the standard deviation of a set of numbers, by recursively calling this function for each value in a value list. 
Use the ValueMean function to calculate the ArithMeanValue.

Example:   ValStdDevS ( "5¶8¶24¶3¶6" ; ValueMean ( "5¶8¶24¶3¶6" ) ) = 8.467585...
			ValStdDevS ( "5" ; ValueMean ( "5¶8¶24¶3¶6" ) ) = ? --> divides by N-1; N=1; so, divide by 0
			ValStdDevS ( "5¶a¶24¶3¶6" ; ValueMean ( "5¶a¶24¶3¶6" ) ) = 9.44986... ['a' resolves as 0]
Bill Thurmes
February 24, 2016
*/

Case ( ValueCount ( ValueList ) > 0 ; 

Let ( [

ValCount = ValueCount ( ValueList ) ;
$$~ValueStdDev = If ( IsEmpty ( $$~ValueStdDev ) ; ValCount & ¶ & 0 ; $$~ValueStdDev ) ;
Data =  GetAsNumber ( GetValue ( ValueList ; 1 ) ) ; 
SumValue = GetValue ( $$~ValueStdDev ; 2 ) ;
TotCount = Max ( ValCount ; GetValue ( $$~ValueStdDev ; 1 ) ) ;
Output = SumValue + ( Data -  ArithMeanValue ) ^ 2 ;

$$~ValueStdDev = TotCount & ¶ & Output ] ;

Case ( ValCount = 1 ; Let ( $$~ValueStdDev = "" ; Sqrt ( Output / ( TotCount - 1 ) ) ) ; ValStdDevS ( RightValues ( ValueList ; ValCount - 1 ) ; ArithMeanValue ) )
) )

The native StDev function in FileMaker only works on fields, but it is often more efficient to work with value lists and do all calculations in memory. This is particularly true when you use ESQL, where the answer is returned as a value list. The ValStdDevS function gives the standard deviation of a set of numerical values in a ¶-delimited value list. This uses the square root of (the sum of squares divided by N-1); for some uses, ValStdDev (which uses N) is more appropriate. One caveat: if a non-numerical value is used, it is evaluated as 0.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ValStdDevS ( ValueList ; ArithMeanValue )

Comments

Support this website.

Support this website.