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