ValCorrelationCoeff ( XValueList ; YValueList ; Xmean ; Ymean ; XStdDev ; YStdDev )

calculates the correlation coefficient for a least-squares fit

Average rating: 4.5 (2 votes) Log in to vote

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

Sample input:

ValCorrelationCoeff ( "3¶3¶6" ; "2¶3¶4" ; 4 ; 3 ; 1.73 ; 1 )

Sample output:

0.87

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

/* =======================
# ValCorrelationCoeff ( XValueList; YValueList; Xmean; Ymean; XStdDev; YStdDev )
# Purpose:		calculates the correlation coefficient for a least-squares fit
# Parameters:	XValueList is a ¶-delimited List of X values (the X of an X,Y pair)
#         		YValueList is a ¶-delimited List of Y values (the Y of an X,Y pair)
#         		Xmean is the mean of all X values
#         		Ymean is the mean of all Y values
#				XStdDev is the standard deviation of all X values
#				YStdDev is the standard deviation of all Y values
#
#				Recursive
#
# Example: ValCorrelationCoeff ( "3¶3¶6" ; "2¶3¶4" ; 4 ; 3 ; 1.73 ; 1 )
# Result: 0.87
#
# If the X- and Y-lists have different ValueCounts, the CF returns "Problem"; a mismatch means that the CF
# might try to associate an incorrect Y value for a given X value.
#
# This CF uses the following variable temporarily during execution, but should null it before the end:
# $~CFv_ValCorrelationCoefficient_sumXY
#
# Author: Bill Thurmes, MDCA
# Creation date:	8/1/2018
============================*/

Let (
	[
	~c = ( ValueCount ( XValueList ) = ValueCount ( YValueList ) ) and ValueCount ( XValueList ) > 0 ; //check that both lists have the same non-zero number of values
	~x = GetValue ( XValueList ; 1 ) ;
	~y = GetValue ( YValueList ; 1 ) ;
	~dx = ~x - Xmean ;
	~dy = ~y - Ymean ;
	$~CFv_ValCorrelationCoefficient_sumXY = $~CFv_ValCorrelationCoefficient_sumXY + ~dx * ~dy ;
	$~CFv_ValCorrelationCoefficient_Ct = Case ( Length ( $~CFv_ValCorrelationCoefficient_Ct );
					$~CFv_ValCorrelationCoefficient_Ct ;
					ValueCount ( XValueList )
					) ;
	~result = Case ( not ~c ;
		"Problem" ; //If there's a problem, return 'Problem'
		
		ValueCount ( XValueList ) > 1 ; //If there are more values, call the function again
		ValCorrelationCoeff ( RightValues ( XValueList ; ValueCount ( XValueList ) - 1 ) ;
			RightValues ( YValueList ; ValueCount ( YValueList ) - 1 ) ; 
			Xmean; 
			Ymean;
			XStdDev;
			YStdDev
		) ; 
		
		$~CFv_ValCorrelationCoefficient_sumXY / ( XStdDev * YStdDev ) / ( $~CFv_ValCorrelationCoefficient_Ct - 1 ) /*If on last value, calculate r*/ 
	) ; //End Case
	$~CFv_ValCorrelationCoefficient_sumXY = Case ( IsEmpty ( ~result ) ; $~CFv_ValCorrelationCoefficient_sumXY ; "" ) ;//If there's a result, null the variables
	$~CFv_ValCorrelationCoefficient_Ct = Case ( IsEmpty ( ~result ) ; $~CFv_ValCorrelationCoefficient_Ct ; "" )
	] ;
	~result
)

I've uploaded a function that calculates the slope of the best-fit line through a set of X,Y paired data, but how well does that line fit the data? This calculates the correlation coefficient for that least-squares fitted line. The result will be between -1 and 1 inclusive, with numbers close to 1 indicating a good fit to a positively sloped line, numbers near -1 indicating a good fit to a negatively sloped line, and number near 0 indicating a poor fit.

Comments

		Chad Calder Dec 29, 2018
Worked great! Thank you!

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

ValCorrelationCoeff ( XValueList ; YValueList ; Xmean ; Ymean ; XStdDev ; YStdDev )

Comments

Support this website.

Support this website.