Regression Numerical Example
Suppose we have the following 5 data points and we want to predict the population data for the year 2005 using linear regression model. In this section, we will use hand calculation or spreadsheet using linear regression formula.
You may download the spreadsheet example of this tutorial here
The formula for linear regression is given below



To use that formula, we make a table consists of four columns. The fist two columns are the data Year as and Population as . The third column is multiplication of for each row and the last column is square of . You can use MS Excel spreadsheet to ease your hand computation.

x 
y 
xy 
x^2 



Year 
Population 


square error 
sq. mean difference 

1980 
2.1 
4158 
3920400 
0.0004 
1.80 

1985 
2.9 
5756.5 
3940225 
0.0196 
0.29 

1990 
3.2 
6368 
3960100 
0.0576 
0.06 

1995 
4.1 
8179.5 
3980025 
0.0004 
0.44 

2000 
4.9 
9800 
4000000 
0.01 
2.13 
sum 
9950 
17.2 
34262 
19800750 
0.088 
4.71 
average 
1990 
3.44 




count n = 
5 





We sum all the five rows and for the first two columns, we calculate the average of year as =1990 and the average of population as = 3.44. Since we have 5 data, then
Thus, we get the regression line
Using this regression line, we can predict the number of population in the city for year 2005. By inputting 2005 to the year we get population = 5.48 (x hundred thousand persons).
The Rsquared value can be computed as
Send your comments, questions and suggestions
Preferable reference for this tutorial is
Teknomo, Kardi (2015) Regression Model using Microsoft Excel. http://people.revoledu.com/kardi/tutorial/Regression/