C# to mimic Excel SLOPE function

Question

I'm trying to calculate the slope of two data lists. You can easily calculate this in EXCEL using the SLOPE function. =SLOPE(A1:A100, B1:B100). I'm trying to mimic this function in C# WinForm. Here is my code, it can calculate something, but not the correct number that you would get from the Excel function. Please help me find the error here. Thanks so much!

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)));
}

Test data:
{1.085231224, 2.335034309, 0.346667278} and
{3.185231224,3.705034309 , -0.883332722} should have slope of 0.3373 if you calculate in Excel using =SLOPE function. But my code produces 0.47 somehow...

{1.085231224, 2.335034309, 0.346667278} and {3.185231224,3.705034309 , -0.883332722} should have slope of 0.3373 if you calculate in Excel using =SLOPE function. But my code produces 0.47 somehow... — Eddie
You are very unclear about the input conditions (what does {1.085231224, 2.335034309, 0.346667278} and {3.185231224,3.705034309 , -0.883332722} mean at all?); about your formula (what does months mean?); and you are not even providing a way to validate the calculations (something which might be easily input in Excel). Can you please improve all this? — varocarbas

D Stanley D Stanley · Accepted Answer · 2013-11-14T20:44:55

I think your formula is wrong

According to the Excel documentation the formula for SLOPE is

Note also that the first argument to the function is the the y values.

It's unclear how goback and months apply, but it looks like this might work:

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;
}

C# to mimic Excel SLOPE function

1 Answers