Least squares/Calculation using Excel

From Wikiversity
Jump to: navigation, search

We can calculate the function f(x) = ax + b that is obtained by applying the Least squares method to a given set of points.

We will first make Excel help us calculate parameters a and b, and later make Excel calculate them by itself, showing that the function it finds is the same we calculated (or, in other words, that Excel used the Least squares method). We will also see how the function obtained matches the set of points fairly well.

"Manual" calculation of the parameters[edit]

First, as said, we will make Excel help us in the calculation of a and b. We introduce our data in columns, and add columns for x_i^2 and x_iy_i. This is the way to make Excel calculate those two columns:

Least squares excell xy.PNG

We must copy the formula to the other cells of the column. Excel is smart enough to adjust the formula so that each value is calculated correctly in each row.

We then instruct Excel to sum these columns:

Least squares excell sum.PNG

(Note Cell B6 should say -7 not -6, if you look at the chart below it plots -7.)

When we have the sums, we calculate a and b using these values:

Least squares excell a.PNG

(Note that the formula should have "5*D7" and "5*C7" instead of "D7" and "C7" respectively.)

"Automatic" calculation of the parameters[edit]

Make a graph[edit]

To make Excel calculate directly the parameters of the least squares fit, we must first make a graph of the points. To do this, select all the x and y values (care not to select the sums) and click on:

Least squares excell insert graph.PNG

We select the graph type "XY (Dispersion)":

Least squares excell graph type.PNG

We can preview the graph to be sure that no incorrect values were selected:

Least squares excell graph preview.PNG

On finishing, our graph should look like this:

Least squares excell graph.PNG

Calculate parameters[edit]

Now we are ready to tell Excel to calculate a Least Squares fit. First we select the points on our graph (by clicking on one of them) and select "Add tendency line" in their context menu:

Least squares excell graph add line.PNG

Make sure the selected type of fit is linear:

Least squares excell graph lineal.PNG

To instruct Excel to show us the a and b parameters that will be used for the fit, go to the "Options" tab and select "Show equation in the graph":

Least squares excell graph show equation.PNG

On clicking "Accept", Excel will calculate a Least Squares fit, show the formula of the line obtained, and plot the line. We can check that the formula of the line plotted by Excel is the same whose parameters a and b we found previously:

Least squares excell graph equation.PNG