ValStdDev ( ValueList ; ArithMeanValue )

Gives the standard deviation of a value List of numbers

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

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

Sample input:

Example: ValStdDev ( "5¶8¶24¶3¶6" ; ValueMean ( "5¶8¶24¶3¶6" ) )
ValStdDev ( "5¶a¶24¶3¶6" ; ValueMean ( "5¶a¶24¶3¶6" ) )

Sample output:

= 7.5736...

= 8.45221... [GetAsNumber sees 'a' as 0]

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

/*
ValStdDev 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'

Format
ValStdDev( 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 custom function to calculate the ArithMeanValue.

Example:   ValStdDev ( "5¶8¶24¶3¶6" ; ValueMean ( "5¶8¶24¶3¶6" ) ) = 7.5736...
			ValStdDev ( "5¶a¶24¶3¶6" ; ValueMean ( "5¶a¶24¶3¶6" ) ) = 8.45221... [GetAsNumber sees 'a' as 0]

Bill Thurmes
Feb 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) ) ; ValStdDev ( 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 ValStdDev 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); for many uses, ValStdDevS (which uses N-1) 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.

ValStdDev ( ValueList ; ArithMeanValue )

Comments

Support this website.

Support this website.