Spreadsheet Example of Linear Regression

by Kardi Teknomo

<Content | Previous | Next >

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.

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 R-squared value can be computed as

<Content | Previous | Next >

Send your comments, questions and suggestions

Preferable reference for this tutorial is

Teknomo, Kardi. Regression Model using Microsoft Excel. http:\\people.revoledu.com\kardi\ tutorial\Regression\

This tutorial is copyrighted.