## Recommended Posts

Posted (edited)

Hello Everybody,

I am looking for the equivalent of the Excel function LINEST in Delphi but I cannot find it. Do you know any library has it?

Many thanks

Alberto

Edited by Alberto Paganini

Posted (edited)

I have found here https://support.office.com/en-gb/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d the formula and I was able to implement my solution:

```program Project3;

{\$APPTYPE CONSOLE}

{\$R *.res}

uses
System.SysUtils;

function Linest(x, y: array of Double): Double;
var
AverageX, AverageY: Double;
Total: Double;
I: Integer;
XMinusAverageX: Double;
YMinusAverageY: Double;
XMinusAverageXSquare: Double;
XMinusAverageXByYMinusAverageY: Double;
TotalXMinusAverageXSquare: Double;
TotalXMinusAverageXByYMinusAverageY: Double;
begin
Total := 0;
for I := 0 to Length(x) - 1 do
Total := Total + x[I];
AverageX := Total / Length(x);

Total := 0;
for I := 0 to Length(y) - 1 do
Total := Total + y[I];
AverageY := Total / Length(y);

TotalXMinusAverageXSquare := 0;
TotalXMinusAverageXByYMinusAverageY := 0;

for I := 0 to Length(x) - 1 do
begin
XMinusAverageX := x[I] - AverageX;
YMinusAverageY := y[I] - AverageY;
XMinusAverageXSquare := Sqr(XMinusAverageX);
XMinusAverageXByYMinusAverageY := XMinusAverageX * YMinusAverageY;
TotalXMinusAverageXSquare := TotalXMinusAverageXSquare + XMinusAverageXSquare;
TotalXMinusAverageXByYMinusAverageY := TotalXMinusAverageXByYMinusAverageY +
XMinusAverageXByYMinusAverageY;
end;
Result := TotalXMinusAverageXByYMinusAverageY / TotalXMinusAverageXSquare;
end;

const
ArrayDimension = 14;

var
x, y: array of Double;
I: Integer;
aResult: Double;

begin
SetLength(x, ArrayDimension);
for I := 0 to Length(x) - 1 do
x[I] := I;

SetLength(y, ArrayDimension);
y := 184.45;
y := 134.71;
y := 157.16;
y := 168.63;
y := 219.83;
y := 247.9;
y := 247.37;
y := 247.37;
y := 343.45;
y := 570.45;
y := 558.21;
y := 559.39;
y := 559.39;
y := 559.39;

aResult := Linest(x, y);
end.```

This is just a simplification of the function because LINEST can accept more parameters but this is what I needed.

Edited by Alberto Paganini
• 1

Nice !

```program Project3;

{\$APPTYPE CONSOLE}

{\$R *.res}

uses
System.SysUtils;

// the result is a slope of the regression line over multiple points
// calculated using simple linear regression
// intercept is the Y-intercept, (Cartesian cordinates system)
function Linest(x, y: array of Double; out intercept: Double): Double;
resourcestring
LINEST_ERROR = 'Linest error : input array of (x,y) should be at least two points';
var
AverageX, AverageY: Double;
Total: Double;
I: Integer;
XMinusAverageX: Double;
YMinusAverageY: Double;
XMinusAverageXSquare: Double;
XMinusAverageXByYMinusAverageY: Double;
TotalXMinusAverageXSquare: Double;
TotalXMinusAverageXByYMinusAverageY: Double;
begin
if (Length(x) = 0) or (Length(x) <> Length(y)) then
raise Exception.CreateRes(@LINEST_ERROR);

Total := 0;
for I := 0 to Length(x) - 1 do
Total := Total + x[I];
AverageX := Total / Length(x);

Total := 0;
for I := 0 to Length(y) - 1 do
Total := Total + y[I];
AverageY := Total / Length(y);

if AverageX = 0 then
begin
intercept := AverageY;
Result := 0;
Exit;
end;

TotalXMinusAverageXSquare := 0;
TotalXMinusAverageXByYMinusAverageY := 0;

for I := 0 to Length(x) - 1 do
begin
XMinusAverageX := x[I] - AverageX;
YMinusAverageY := y[I] - AverageY;
XMinusAverageXSquare := Sqr(XMinusAverageX);
XMinusAverageXByYMinusAverageY := XMinusAverageX * YMinusAverageY;
TotalXMinusAverageXSquare := TotalXMinusAverageXSquare + XMinusAverageXSquare;
TotalXMinusAverageXByYMinusAverageY := TotalXMinusAverageXByYMinusAverageY +
XMinusAverageXByYMinusAverageY;
end;

Result := TotalXMinusAverageXByYMinusAverageY / TotalXMinusAverageXSquare;
intercept := AverageY - Result * AverageX;
end;

const
ArrayDimension = 14;

var
x, y: array of Double;
I: Integer;
aResult: Double;
aIntecept: Double;

begin
SetLength(x, ArrayDimension);
for I := 0 to Length(x) - 1 do
x[I] := I;

SetLength(y, ArrayDimension);
y := 184.45;
y := 134.71;
y := 157.16;
y := 168.63;
y := 219.83;
y := 247.9;
y := 247.37;
y := 247.37;
y := 343.45;
y := 570.45;
y := 558.21;
y := 559.39;
y := 559.39;
y := 559.39;

aResult := Linest(x, y, aIntecept);
end.```

• 1

Hi!

You could use the regression functions from here:

https://github.com/mikerabat/mrmath

and the regression example from here:

• 1