Linear Regression

This very brief definition, aims only at giving a concrete example (through formulas) of simple linear regression calculation.

In statistics, it happens that two values X and Y seem bound by a linear function relation of the type Y = a · X + b.

The linear regression consists in determining an estimation of the a and b values.

Let us take the following X and Y data as example:

We have Y = a.X + B

We need the following data to calculate a and b:

• X Average = 46,25

• Y Average = 218,75

• Average of x2 = 3400

• Average of y2 = 76568,75

• Variance x = Average of x2 – square of x average = 1260,94

• Variance y = Average of y2 – square of y average = 8717,19

• Covariance = Average of products – products of averages = 5907,81

a = Covariance / Variance x = 4,69

b = y average - a * x average = 2,06

Y = 4,69 X + 2,06

Excel utilisation

If:

• X values are in cells A1 to A8

• Y values are in cells B1 to B8

a is obtained with the following formula =LINEST(B1:B8;A1:A8)

b is obtained with the following formula =AVERAGE(B1:B8)-LINEST(B1:B8;A1:A8)*AVERAGE(A1:A8)