How do I recreate an Excel formula which calls TRE

问题:

I'm building a .net page to mimic a spreadsheet. The sheet contains this formula

=ROUND(TREND(AA7:AE7,AA$4:AE$4,AF$4),1)

Can someone provide the C# equivalent of TREND() ? Alternatively if anyone can provide a shortcut around it that's fine too; I'm not familiar enough with the math there to know if there's an easier way.

Here are some sample numbers if it helps.

AA7:AE7 6 8 10 12 14

or 10.2 13.6 17.5 20.4 23.8

AA$4:AE$4 600 800 1000 1200 1400

AF$4 650

edit: here's what I came up with and it seems to be producing the same numbers as my spreadsheet.

public static partial class Math2
{
    public static double[] Trend(double[] known_y, double[] known_x, params double[] new_x)
    {
        // return array of new y values
        double m, b;
        Math2.LeastSquaresFitLinear(known_y, known_x, out m, out b);

        List<double> new_y = new List<double>();
        for (int j = 0; j < new_x.Length; j++)
        {
            double y = (m * new_x[j]) + b;
            new_y.Add(y);
        }

        return new_y.ToArray();
    }

    // found at http://stackoverflow.com/questions/7437660/how-do-i-recreate-an-excel-formula-which-calls-trend-in-c
    // with a few modifications
    public static void LeastSquaresFitLinear(double[] known_y, double[] known_x, out double M, out double B)
    {
        if (known_y.Length != known_x.Length)
        {
            throw new ArgumentException("arrays are unequal lengths");
        }

        int numPoints = known_y.Length;

        //Gives best fit of data to line Y = MC + B
        double x1, y1, xy, x2, J;

        x1 = y1 = xy = x2 = 0.0;
        for (int i = 0; i < numPoints; i++)
        {
            x1 = x1 + known_x[i];
            y1 = y1 + known_y[i];
            xy = xy + known_x[i] * known_y[i];
            x2 = x2 + known_x[i] * known_x[i];
        }

        M = B = 0;
        J = ((double)numPoints * x2) - (x1 * x1);

        if (J != 0.0)
        {
            M = (((double)numPoints * xy) - (x1 * y1)) / J;
            //M = Math.Floor(1.0E3 * M + 0.5) / 1.0E3; // TODO this is disabled as it seems to product results different than excel
            B = ((y1 * x2) - (x1 * xy)) / J;
            // B = Math.Floor(1.0E3 * B + 0.5) / 1.0E3; // TODO assuming this is the same as above
        }
    }

}

回答1:

Consider TREND is based upon the Excel Function, LINEST. If you follow this link, https://support.office.com/en-us/article/LINEST-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d, it will explain the functionality behind LINEST.

In addition, you'll find the base formula that it uses.

.

回答2:

This post has been very helpful as we've needed to recreate this in C#. Thanks to Jeff's answer above I've recreated that formula using the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Drawing;

public static class MathHelper
{
    /// <summary>
    /// Gets the value at a given X using the line of best fit (Least Square Method) to determine the equation
    /// </summary>
    /// <param name="points">Points to calculate the value from</param>
    /// <param name="x">Function input</param>
    /// <returns>Value at X in the given points</returns>
    public static float LeastSquaresValueAtX(List<PointF> points, float x)
    {
        float slope = SlopeOfPoints(points);
        float yIntercept = YInterceptOfPoints(points, slope);

        return (slope * x) + yIntercept;
    }

    /// <summary>
    /// Gets the slope for a set of points using the formula:
    /// m = ∑ (x-AVG(x)(y-AVG(y)) / ∑ (x-AVG(x))²
    /// </summary>
    /// <param name="points">Points to calculate the Slope from</param>
    /// <returns>SlopeOfPoints</returns>
    private static float SlopeOfPoints(List<PointF> points)
    {
        float xBar = points.Average(p => p.X);
        float yBar = points.Average(p => p.Y);

        float dividend = points.Sum(p => (p.X - xBar) * (p.Y - yBar));
        float divisor = (float)points.Sum(p => Math.Pow(p.X - xBar, 2));

        return dividend / divisor;            
    }

    /// <summary>
    /// Gets the Y-Intercept for a set of points using the formula:
    /// b = AVG(y) - m( AVG(x) )
    /// </summary>
    /// <param name="points">Points to calculate the intercept from</param>
    /// <returns>Y-Intercept</returns>
    private static float YInterceptOfPoints(List<PointF> points, float slope)
    { 
        float xBar = points.Average(p => p.X);
        float yBar = points.Average(p => p.Y);

        return yBar - (slope * xBar);        
    }       
}

Since Point uses Integers to define its values I've elected to use PointF since in our applications, there can be many decimal places. Pardon any math terminology that is off as I spend more time writing code than developing algorithms like this although I'd love anyone to correct me should I have mistated a term somewhere.

This is certainly faster than waiting for Excel Interop to load up in the background to use the workbook's Trend method.