What is the C# equivalent to LINEST from Excel?

Question

Is any inbuit function is there or we need to write our own. In later case could you please give me some link where it has been implemented. And how it works?

Thanks

Answer 1

There's no built-in functionality in C# to calculate the best fit line using the least squares method. I wouldn't expect there to be one either since Excel is used for data manipulation/statistics and C# is a general purpose programming language.

There are plenty of people that have posted implementations to various sites though. I'd suggest checking them out and learning the algorithm behind their calculations.

Here's a link to one implementation:

Maths algorithms in C#: Linear least squares fit

Answer 2

There is pretty extensive documentation in the Online Help. And no, this is not available in C# by default. Both C#/.NET and Excel have quite differing uses, hence the different feature set.

Answer 3

Having attempted to solve this problem using this question and other questions which are similar/the same, I couldn't get a good example of how to accomplish this. However, pooling many posts (and Office Help's description of what LINEST actually does) I thought I would post my solution code.

    /// <summary>
    /// Finds the Gradient using the Least Squares Method
    /// </summary>
    /// <returns>The y intercept of a trendline of best fit through the data X and Y</returns>
    public decimal LeastSquaresGradient()
    {

        //The DataSetsMatch method ensures that X and Y 
        //(both List<decimal> in this situation) have the same number of elements
        if (!DataSetsMatch())
        {
            throw new ArgumentException("X and Y must contain the same number of elements");
        }

        //These variables are used store the variances of each point from its associated mean
        List<decimal> varX = new List<decimal>();
        List<decimal> varY = new List<decimal>();

        foreach (decimal x in X)
        {
            varX.Add(x - AverageX());
        }
        foreach (decimal y in Y)
        {
            varY.Add(y - AverageY());
        }

        decimal topLine = 0;
        decimal bottomLine = 0;

        for (int i = 0; i < X.Count; i++)
        {
            topLine += (varX[i] * varY[i]);
            bottomLine += (varX[i] * varX[i]);
        }

        if (bottomLine != 0)
        {
            return topLine / bottomLine;
        }
        else
        {
            return 0;
        }
    }

    /// <summary>
    /// Finds the Y Intercept using the Least Squares Method
    /// </summary>
    /// <returns>The y intercept of a trendline of best fit through the data X and Y</returns>
    public decimal LeastSquaresYIntercept()
    {
        return AverageY() - (LeastSquaresGradient() * AverageX());
    }



    /// <summary>
    /// Averages the Y.
    /// </summary>
    /// <returns>The average of the List Y</returns>
    public decimal AverageX()
    {
        decimal temp = 0;
        foreach (decimal t in X)
        {
            temp += t;
        }

        if (X.Count == 0)
        {
            return 0;
        }
        return temp / X.Count;
    }

    /// <summary>
    /// Averages the Y.
    /// </summary>
    /// <returns>The average of the List Y</returns>
    public decimal AverageY()
    {
        decimal temp = 0;
        foreach (decimal t in Y)
        {
            temp += t;
        }

        if (Y.Count == 0)
        {
            return 0;
        }

        return temp / Y.Count;
    }

Answer 4

Here's an implementation of Excel's LINEST() function in C#. It returns the slope for a given set of data, normalized using the same "least squares" method that LINEST() uses:

public static double CalculateLinest(double[] y, double[] x)
{
   double linest = 0;
   if (y.Length == x.Length)
   {
      double avgY = y.Average();
      double avgX = x.Average();
      double[] dividend = new double[y.Length];
      double[] divisor = new double[y.Length];
      for (int i = 0; i < y.Length; i++)
      {
         dividend[i] = (x[i] - avgX) * (y[i] - avgY);
         divisor[i] = Math.Pow((x[i] - avgX), 2);
      }
      linest = dividend.Sum() / divisor.Sum();
   }
   return linest;
}

Also, here's a method I wrote to get the "b" (y-intercept) value that Excel's LINEST function generates.

private double CalculateYIntercept(double[] x, double[] y, double linest)
{
    return (y.Average() - linest * x.Average());
}

Since these methods only work for one set of data, I would recommend calling them inside of a loop if you wish to produce multiple sets of linear regression data.

This link helped me find my answer: https://agrawalreetesh.blogspot.com/2011/11/how-to-calculate-linest-of-given.html