26
$\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.

$\endgroup$

3 Answers 3

32
$\begingroup$

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

$\endgroup$
1
  • 3
    $\begingroup$ Source? ${}{}{}$ $\endgroup$
    – Rasmus
    Sep 28, 2012 at 15:02
1
$\begingroup$

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$.

$\endgroup$
0
-2
$\begingroup$

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.

$\endgroup$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.