What is the equation used to calculate a linear trendline?

Question

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

Answer 1

Given a set of ordered Pairs (x,y) set up the following table with these columns

X Y X^2 X*Y estimate = M*x+B error= Y - estimate

fill in the numbers

complete the table

calculate the totals of each column

Here are the formulas:

N = # of ordered pairs (NOT counting the totals)

denominator D = N * sum of X^2 - ( sum of X)^2

slope M = (N * total of XY- total of X * total of Y)/D

intercept B = (total of Y * total of X^2 - total of X * total XY)/D

line estimate = Mx+B

error = Y - estimate

================================================================

Here is an example for the line y = 2x + 1 using the data points (1,3) (2,5) and (-1,-1)

X Y X^2 XY line estimate error

1 3 1 3 3 0

2 5 4 10 5 0

-1 -1 1 1 -1 0

====================================================================

TOTAL 2 7 6 14 7 0

N=3

D = 3*6 - 2^2 = 18 - 4 = 14

m = (3 * 14 - 2*7)/14 = (42 - 14)/14 = 28/14 = 2

b = (7*6 - 2*14)/14 = (42-28)/14 = 14/14 = 1

y = 2x+1