# LinearTrend ( )

Function stats

Average user rating
5.0000
Support
FileMaker 8.5 +
Date posted
10 December 2008
Last updated
13 January 2014
Version
Recursive function
No

Author Info
Andries Heylen

11 functions

Average Rating 4.9 Function overview

Prototype

LinearTrend  ( _yValues;   _xValues )

Parameters

_yValues  List of Y-values

_xValues  List of X-values

Description

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

LinearTrend ( List ( 1 ; 2 ; 3 ; 4 ) ; List ( 3 ; 5 ; 6 ; 9 ) )

Sample output

2,9¶
4,8¶
6,7¶
8,6

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]

Function requires:

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 )
; \$fn = _a & " + " & _b & " * x"
]
;
CustomList ( 1 ; ValueCount ( \$listX ) ; "Evaluate ( Substitute ( \$fn ; \"x\" ; GetValue ( \$listX ; [n] ) ) )" )

)

// ===================================
/*

This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports

Prototype: LinearTrend( _yValues; _xValues )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 13 January 2014
Version: 2.1

*/
// =================================== WilliamCB
02 July 2009

Fantastic range of functions here, doing lots of things I've dreamt of for years! Well done!

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.

Bug report Andries Heylen
22 July 2009

Hi William

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 )
General comment WilliamCB2
28 July 2009

On further investigation, I discovered there is no problem with this function. Rather, I failed to realise that it requires another custom function to be installed first.

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..."
General comment Jeffrey Mothersbaugh
15 December 2014

Can anyone help me to implement LinearTrend? I seem to be getting an error with CustomList stating that:

[Error_CL] | Return error : 102 | Field is missing
Calculation ( for [n] = 1 ) : Evaluate ( Substitute ( \$fn ; "x" ; GetValue ( \$listX ; 1 ) ) )

I am not sure how to cure this.

Jeff
(Edited by Jeffrey Mothersbaugh on 16/12/14 )
General comment

# Related Functions

Required functions