How to calculate a trend line for a chart?

Question

Google is not my friend - it has been a long time since my statistics class is in college ... I need to calculate the start and end points for the trend line on the chart - is there an easy way to do this? (works in C #, but works in any language for you)

Answer 1

Given that the trend line is straight, find the slope by selecting any two points and calculating:

(A) slope = (y1-y2) / (x1-x2)

Then you need to find the offset for the string. The line is given by the equation:

(B) y = offset + tilt * x

So you need to decide for the offset. Select any point on the line and decide to offset:

(C) offset = y / (tilt * x)

Now you can connect the slope and offset in linear equation (B) and have an equation defining your line. If your line has noise, you have to decide the averaging algorithm or use some kind of approach.

If your line is not straight, you will need to study Curve fitting or Least Square Fixing - non-trivial but skillful. You will see various types of fittings at the bottom of the webpage with the least squares (exponential, polynomial, etc.), if you know what shape you want.

Also, if it is a one-time use Excel.

-Adam

Answer 2

Thank you all for your help. I tried this problem for a couple of days and just returned to it - I was able to bend it together - not the most elegant code, but it works for my purposes - I thought d, if anyone else encounters this problem:

public class Statistics { public Trendline CalculateLinearRegression(int[] values) { var yAxisValues = new List<int>(); var xAxisValues = new List<int>(); for (int i = 0; i < values.Length; i++) { yAxisValues.Add(values[i]); xAxisValues.Add(i + 1); } return new Trendline(yAxisValues, xAxisValues); } } public class Trendline { private readonly IList<int> xAxisValues; private readonly IList<int> yAxisValues; private int count; private int xAxisValuesSum; private int xxSum; private int xySum; private int yAxisValuesSum; public Trendline(IList<int> yAxisValues, IList<int> xAxisValues) { this.yAxisValues = yAxisValues; this.xAxisValues = xAxisValues; this.Initialize(); } public int Slope { get; private set; } public int Intercept { get; private set; } public int Start { get; private set; } public int End { get; private set; } private void Initialize() { this.count = this.yAxisValues.Count; this.yAxisValuesSum = this.yAxisValues.Sum(); this.xAxisValuesSum = this.xAxisValues.Sum(); this.xxSum = 0; this.xySum = 0; for (int i = 0; i < this.count; i++) { this.xySum += (this.xAxisValues[i]*this.yAxisValues[i]); this.xxSum += (this.xAxisValues[i]*this.xAxisValues[i]); } this.Slope = this.CalculateSlope(); this.Intercept = this.CalculateIntercept(); this.Start = this.CalculateStart(); this.End = this.CalculateEnd(); } private int CalculateSlope() { try { return ((this.count*this.xySum) - (this.xAxisValuesSum*this.yAxisValuesSum))/((this.count*this.xxSum) - (this.xAxisValuesSum*this.xAxisValuesSum)); } catch (DivideByZeroException) { return 0; } } private int CalculateIntercept() { return (this.yAxisValuesSum - (this.Slope*this.xAxisValuesSum))/this.count; } private int CalculateStart() { return (this.Slope*this.xAxisValues.First()) + this.Intercept; } private int CalculateEnd() { return (this.Slope*this.xAxisValues.Last()) + this.Intercept; } } 

Answer 3

OK, here is my best pseudo-matra:

The equation for your line is:

Y = a + bX

Where:

b = (sum (x * y) - sum (x) sum (y) / n) / (sum (x ^ 2) - sum (x) ^ 2 / n)

a = sum (y) / n - b (sum (x) / n)

Where the sum (xy) is the sum of all x * y, etc. I don’t really understand that I agree, but this is the best I can do without the sigma symbol :)

... and now with the addition of Sigma

b = (? Sigma; (xy) - (? Sigma; x & Sigma; y) / n) / (? Sigma; (x ^ 2) - (? Sigma; x) ^ 2 / n)

a = (? Sigma; y) / n - b ((? Sigma; x) / n)

Where & Sigma; (xy) is the sum of all x * y, etc., and n is the number of points

Answer 4

Here's a very quick (and semi-dirty) implementation of Bedwir Humphries' answer . The interface should be compatible with @matt's answer, but use decimal instead of int and use more IEnumerable concepts to hopefully simplify their use and read.

Slope is b , Intercept is a

 public class Trendline { public Trendline(IList<decimal> yAxisValues, IList<decimal> xAxisValues) : this(yAxisValues.Select((t, i) => new Tuple<decimal, decimal>(xAxisValues[i], t))) { } public Trendline(IEnumerable<Tuple<Decimal, Decimal>> data) { var cachedData = data.ToList(); var n = cachedData.Count; var sumX = cachedData.Sum(x => x.Item1); var sumX2 = cachedData.Sum(x => x.Item1 * x.Item1); var sumY = cachedData.Sum(x => x.Item2); var sumXY = cachedData.Sum(x => x.Item1 * x.Item2); //b = (sum(x*y) - sum(x)sum(y)/n) // / (sum(x^2) - sum(x)^2/n) Slope = (sumXY - ((sumX * sumY) / n)) / (sumX2 - (sumX * sumX / n)); //a = sum(y)/n - b(sum(x)/n) Intercept = (sumY / n) - (Slope * (sumX / n)); Start = GetYValue(cachedData.Min(a => a.Item1)); End = GetYValue(cachedData.Max(a => a.Item1)); } public decimal Slope { get; private set; } public decimal Intercept { get; private set; } public decimal Start { get; private set; } public decimal End { get; private set; } public decimal GetYValue(decimal xValue) { return Intercept + Slope * xValue; } } 

Answer 5

Regarding the previous answer

if (B) y = offset + tilt * x

then (C) offset = y / (slope * x) is invalid

(C) should be:

offset = y- (tilt * x)

See: http://zedgraph.org/wiki/index.php?title=Trend

Answer 6

If you have access to Excel, see the "Statistical Functions" section of the function reference in the Help. For a straight line of best fit, you need SLOPE and INTERCEPT, and the equations are right there.

Oh hold on, they are also defined here: http://office.microsoft.com/en-us/excel/HP052092641033.aspx for SLOPE, and there is a link to INTERCEPT. Of course, it is assumed that MS is not moving the page, in which case try Googling for something like "SLAPE INTERCEPT EQUATION Excel site: microsoft.com" - the link shown now is the third.

Answer 7

Thanks so much for the decision, I'm scratching my head.
This is how I applied the solution in Excel.
I have successfully used two functions defined by MUHD in Excel:
a (sum (x * y) - sum (x) sum (y) / n) / (sum (x ^ 2) - sum (x) ^ 2 / n)
b = sum (y) / n - b (sum (x) / n)
(my a and b are careful - this is b and a in the MUHD solution).

- 4 columns made, for example:
NB: the values ​​of my y values ​​are in B3: B17, so I n = 15;
my x values ​​are 1,2,3,4 ... 15.
1. Column B: Famous x's 2. Column C: Famous y's 3. Column D: Calculated trend line
4. The values ​​of the columns E: B * C (E3 = B3 * C3, E4 = B4 * C4, ..., E17 = B17 * C17)
5. Column F: x squared values
Then I summarize columns B, C and E, the amounts go to row 18 for me, so I have B18 as the sum of Xs, C18 as the sum of Ys, E18 as the sum of X * Y and F18 as the sum of squares.
To calculate a, enter the following formula in any cell (F35 for me):
F35 = (E18- (B18 * C18) / 15) / (F18- (B18 * B18) / 15)
To calculate b (in F36 for me):
F36 = C18 / 15-F35 * (B18 / 15)
The values ​​of column D, calculating the trend line in accordance with y = ax + b:
D3 = $ F $ 35 * B3 + $ F $ 36, D4 = $ F $ 35 * B4 + $ F $ 36, etc. (Before D17 for me).

Select the columns (C2: D17) to plot.
NTN.