Introduction to Computing

| HOME | SYLLABUS | CALENDAR AND EXERCISES |

EXCEL spreadsheet program

Linear regression

Linear regression is a form of regression analysis in which the relationship between one or more independent variables and another variable, called dependent variable, is modeled by a least squares function, called linear regression equation. This function is a linear combination of one or more model parameters, called regression coefficients. A linear regression equation with one independent variable represents a straight line.

Enter the following data to column A starting from A4 up to A9 and to column B starting from B4 up to B9: :
4,512,6
6,016,8
10,221,8
11,825,5
15,929,4
17,337,2

Add title to cell A1 "Observation data" and to A3 "X-axis" and to B3 "Y-axis".

We apply the least squares approach in order to get the coefficients a and b to the equation y = ax + b.

LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line. Because this function returns an array of values, it must be entered as an array formula. After typing an array formula, press simultaneously SHIFT+CRTL+Enter.

Enter to cell D1: "The least squares line of best fit " and to cell D2 "y= ax + b ". Enter to D3 "coefficient a" and to E3 "constant b".

Select area D4:E4. Type the equation "=LINEST(B4:B9;A4:A9)" and then press SHIFT+CRTL+Enter. Now you should see the values in these two cells.

Another way to enter the equation is to use the function wizard and to select areas of x and y values.The function wizard starts with insert function (symbol fx). When you have entered the ranges, remember to press SHIFT+CRTL+Enter.

Create a scatter chart based on values in A4:B9 on a separate chart sheet, right-click Move Chart1. Select Chart Tools, Layout, and add a title "Least squares fit of observation data". Add a trendline to your chart Chart - Layout - Analysis - - Add linear trendline. Select More trendline options and from there tick "Display equation on chart".

Next, calculate the points that the equation gives based on values of a and b. The new line does not necessarily touch actual data points.

Type to cell A11 "Values based on the least squares equation" and to cells A13 and B13
"X-axis" and "Y-axis" . Copy the actual data (from A4:A9) to cells A14:A19.

Type to B14 "=A14*$D$4+$E$4" the instruction to calculate first y point based on y=a*x+b. Dollar signs create an absolute reference, use F4.

Copy the formula to cells B15:B19.
Finally, add the values to the chart

Now the chart displays original values as well as calculated values.

| HOME | SYLLABUS | CALENDAR AND EXERCISES |

Created by: Jaana Holvikivi
Updated: 24.10.2013