LINEST function

[Alberto Paganini 3]

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 May 10, 2020 by Alberto Paganini

[Alberto Paganini 3]

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[0] := 184.45;
  y[1] := 134.71;
  y[2] := 157.16;
  y[3] := 168.63;
  y[4] := 219.83;
  y[5] := 247.9;
  y[6] := 247.37;
  y[7] := 247.37;
  y[8] := 343.45;
  y[9] := 570.45;
  y[10] := 558.21;
  y[11] := 559.39;
  y[12] := 559.39;
  y[13] := 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 May 10, 2020 by Alberto Paganini

[Guest]

Nice !

 

I added the intercept calculation, someone might find this helpful.

 

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[0] := 184.45;
  y[1] := 134.71;
  y[2] := 157.16;
  y[3] := 168.63;
  y[4] := 219.83;
  y[5] := 247.9;
  y[6] := 247.37;
  y[7] := 247.37;
  y[8] := 343.45;
  y[9] := 570.45;
  y[10] := 558.21;
  y[11] := 559.39;
  y[12] := 559.39;
  y[13] := 559.39;

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

 

[mikerabat 20]

Hi!

 

You could use the regression functions from here:

https://github.com/mikerabat/mrmath

and the regression example from here:

http://www.mrsoft.org/home/downloads.html