22
$\begingroup$

What are the equations to calculate a linear trendline over a set of points?


EDIT: "In excel it is done automatically but how to manually calculate a linear trendline over a set of points" was originally the question. At first I asked this question because I was simply doing it with excel all the time and couldn't figure out how it computed the result. People tend to focus on the excel part instead of the actual question so I just removed this mention.

3 Answers 3

26

I've been looking for this formula in this website so I've made it Q&A style :) Hope this helps.

Slope;

$\alpha = {n\sum(xy) - \sum x \sum y \over n\sum x^2 - (\sum x)^2}$

Offset:

$\beta = {\sum y - \alpha \sum x \over n}$

Trendline formula:

$y = \alpha x + \beta $

source : https://classroom.synonym.com/calculate-trendline-2709.html

  • 0
    @TecHunter : Is there a reason why you put the "equals" signs _outside_ of MathJax? (And also put purposeless curly braces around \alpha and \beta?). Putting "=" outside of MathJax results in lack of font size matching and proper spacing. ${}\qquad{}$2015-11-19
1

For Excel, let $r$ be Pearson's r, $s_x$ the standard deviation and $\bar{X}_x$ the mean of all the numbers on the x-axis, $s_y$ the standard deviation and $\bar{X}_y$ the mean of all the numbers on the y-axis.

Then the slope will be $a = r \frac{s_y}{s_x}$ and y-intercept $b = \bar{X}_y - a \bar{X}_x$, for linear trendline $y = ax + b$.

  • 0
    Thanks for your answer, I would like to understand it but my level of mathematics seems too low. could you detail more? `Pearson's r`? `standard deviation`? thanks!2018-12-04
-1

When I have used the data analysis package for simple linear regression I always get a scatterplot of the data and have an option to add a trendline and also an option for the equation and R square to be printed on the graph. these days this can easily be done with the chart wizard.

  • 4
    Because I need it for a javascript program where I don't have excel and my points are not static data. You have a calculator that does a lot of things yet sometimes you need to know how to do this by yourself no? anyway if you don't need this maybe someone else like me would need it2012-09-28