C#模仿Excel SLOPE函数

Question

我正在尝试计算两个数据列表的斜率。您可以使用 SLOPE 函数在 EXCEL 中轻松计算。 =斜率(A1:A100，B1:B100)。我试图在 C# WinForm 中模仿这个函数。这是我的代码，它可以计算一些东西，但不是您从 Excel 函数中获得的正确数字。请帮我找出这里的错误。非常感谢!

private double Getslope(List<double> ProductGrossExcessReturnOverRFR, List<double> primaryIndexExcessReturnOverRFR, int months, int go_back = 0)
{
    double slope = 0;
    double sumx = 0, sumy = 0, sumxy = 0, sumx2 = 0;
    for (int i = ProductGrossExcessReturnOverRFR.Count - 1 - go_back; i > ProductGrossExcessReturnOverRFR.Count - (1 + months + go_back); i--)
    {
        sumxy += ProductGrossExcessReturnOverRFR[i] * primaryIndexExcessReturnOverRFR[i];
        sumx += ProductGrossExcessReturnOverRFR[i];
        sumy += primaryIndexExcessReturnOverRFR[i];
        sumx2 += ProductGrossExcessReturnOverRFR[i] * ProductGrossExcessReturnOverRFR[i];      
    }

    return slope = 1 / (((sumxy - sumx * sumy / months) / (sumx2 - sumx * sumx / months)));
}

测试数据:
{1.085231224, 2.335034309, 0.346667278} 和
如果您在 Excel 中使用 =SLOPE 函数进行计算，{3.185231224,3.705034309 , -0.883332722} 的斜率为 0.3373。但是我的代码以某种方式产生了 0.47...

Answer 1

我认为你的公式是错误的

根据 Excel 文档，SLOPE 的公式为

另请注意，函数的第一个参数是 y 值。

目前还不清楚 goback 和 months 是如何应用的，但它看起来可能会起作用:

private double Getslope(List<double> ProductGrossExcessReturnOverRFR, 
                        List<double> primaryIndexExcessReturnOverRFR, 
                        int months, 
                        int go_back = 0)
{
    // calc # of items to skip
    int skip = ProductGrossExcessReturnOverRFR.Count - go_back - months;

    // get list of x's and y's
    var ys = ProductGrossExcessReturnOverRFR.Skip(skip).Take(months);
    var xs = primaryIndexExcessReturnOverRFR.Skip(skip).Take(months);

    // "zip" xs and ys to make the sum of products easier
    var xys = Enumerable.Zip(xs,ys, (x, y) => new {x = x, y = y});

    double xbar = xs.Average();
    double ybar = ys.Average();

    double slope = xys.Sum(xy => (xy.x - xbar) * (xy.y - ybar)) / xs.Sum(x => (x - xbar)*(x - xbar));

    return slope;
}