LinearTrend ( )
Function stats
Average user rating
Support
FileMaker 8.5 +
Date posted
10 December 2008
Last updated
10 December 2008
Version
1.3 + View history
Recursive function
No
Function overview
Prototype
LinearTrend (
_yValues; _xValues )
Parameters
_yValues List of Y-values
_xValues List of X-values
Description
Tags: Regression Linear Graph
For a given dataset { ( X , Y ) ; ( X , Y ) ; ... ; ( X, Y ) } this function returns the linear regression equation based on the least square method.
y = B + A x
Very useful to find trends in datasets, or to plot the trendline in a graph.
Examples
Sample input
_yValues = List ( 100 ; 150 ; 200 ; 400 ; 200 ; 200 ; 200 ; 100 ; 300 )
_xValues = List ( 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 )
_xValues = List ( 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 )
Sample output
y = 155,36 + 11,31 x
Function code
/*
For a given dataset { ( X1 , Y1 ) ; ( X2 , Y2 ) ; ... ; ( Xn , Yn ) } this function returns the linear regression equation based on the least square method.
y = B + A x
Very useful to find trends in datasets, or to plot the trendline in a graph.
Developped by : Andries Heylen [BH&A]
mail: andries_heylen@bh-a.com
v1.0 08/12/2008 Andries Heylen [BH&A]
Example:
input:
_yValues = List ( 100 ; 150 ; 200 ; 400 ; 200 ; 200 ; 200 ; 100 ; 300 )
_xValues = List ( 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 )
returns:
y = 155,36 + 11,31 x
Function uses:
CustomList by Agnes:
http://www.fmcustomfunctions.com/fid/118
*/
Let (
[
$listY = _yValues
; _Nmb = ValueCount ( $ListY )
; $listX = _xValues
; _SerieX = Substitute ( $listX ; ¶ ; ";" )
; _SumX = Evaluate ( "Sum ( " & _SerieX & ")" )
; _SerieY = Substitute ( $listY ; ¶ ; ";" )
; _SumY = Evaluate ( "Sum ( " & _SerieY & ")" )
; _ListXY = CustomList ( 1 ; _Nmb ; "GetValue ( $ListX ; [n] ) * GetValue ( $ListY ; [n] )" )
; _SerieXY = Substitute ( _ListXY ; ¶ ; ";" )
; _SumXY = Evaluate ( "Sum ( " & _SerieXY & " ) " )
; _ListX2 = CustomList ( 1 ; _Nmb ; "GetValue ( $ListX ; [n] ) ^ 2" )
; _SerieX2 = Substitute ( _ListX2 ; ¶ ; ";" )
; _SumX2 = Evaluate ( "Sum ( " & _SerieX2 & " ) " )
; _b = Round ( ( _Nmb * _SumXY - _SumX * _SumY ) / ( _Nmb * _SumX2 - _SumX ^2 ) ; 2 )
; _a = Round ( ( _SumY - _b * _SumX ) / _Nmb ; 2 )
]
;
"y = " & _a & " + " & _b & "x"
)
// ===================================
/*
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/135
Prototype: LinearTrend( _yValues; _xValues )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 10 December 2008
Version: 1.3
*/
// ===================================
For a given dataset { ( X1 , Y1 ) ; ( X2 , Y2 ) ; ... ; ( Xn , Yn ) } this function returns the linear regression equation based on the least square method.
y = B + A x
Very useful to find trends in datasets, or to plot the trendline in a graph.
Developped by : Andries Heylen [BH&A]
mail: andries_heylen@bh-a.com
v1.0 08/12/2008 Andries Heylen [BH&A]
Example:
input:
_yValues = List ( 100 ; 150 ; 200 ; 400 ; 200 ; 200 ; 200 ; 100 ; 300 )
_xValues = List ( 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 )
returns:
y = 155,36 + 11,31 x
Function uses:
CustomList by Agnes:
http://www.fmcustomfunctions.com/fid/118
*/
Let (
[
$listY = _yValues
; _Nmb = ValueCount ( $ListY )
; $listX = _xValues
; _SerieX = Substitute ( $listX ; ¶ ; ";" )
; _SumX = Evaluate ( "Sum ( " & _SerieX & ")" )
; _SerieY = Substitute ( $listY ; ¶ ; ";" )
; _SumY = Evaluate ( "Sum ( " & _SerieY & ")" )
; _ListXY = CustomList ( 1 ; _Nmb ; "GetValue ( $ListX ; [n] ) * GetValue ( $ListY ; [n] )" )
; _SerieXY = Substitute ( _ListXY ; ¶ ; ";" )
; _SumXY = Evaluate ( "Sum ( " & _SerieXY & " ) " )
; _ListX2 = CustomList ( 1 ; _Nmb ; "GetValue ( $ListX ; [n] ) ^ 2" )
; _SerieX2 = Substitute ( _ListX2 ; ¶ ; ";" )
; _SumX2 = Evaluate ( "Sum ( " & _SerieX2 & " ) " )
; _b = Round ( ( _Nmb * _SumXY - _SumX * _SumY ) / ( _Nmb * _SumX2 - _SumX ^2 ) ; 2 )
; _a = Round ( ( _SumY - _b * _SumX ) / _Nmb ; 2 )
]
;
"y = " & _a & " + " & _b & "x"
)
// ===================================
/*
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/135
Prototype: LinearTrend( _yValues; _xValues )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 10 December 2008
Version: 1.3
*/
// ===================================
Login or register to comment
Create a new account with fmcustomfunctions.com or login to post a comment.
Related Functions
Comments
02 July 2009
In this case I have a problem. When I copy and paste this code in, I get an error report in the line:
; _SumY = Evaluate ( "Sum ( " & _SerieY & ")" )
with the blue highlight on "Sum ( "
I've checked the variables are entered correctly.
FM10.0 v1 Advanced.
22 July 2009
I tried to see what the error might be, but I don't understand the problem.
Can you provide me the x-values and y-values you are trying to evaluate?
Did you try the example given? this should work normally.
Thanks
Andries
(Edited by Andries Heylen on 22/07/09 )
27 July 2009
As a first time user of custom functions, I think I was misled by the following text:
"Function uses:
CustomList by Agnes:
http://www.fmcustomfunctions.com/fid/118"
This means "This function relies upon...", but I took it to mean "This function can be applied to..."