View source for Least squares/Calculation using Excel

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 ==

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 <math>x_i^2</math> and <math>x_iy_i</math>. This is the way to make Excel calculate those two columns:

[[Image: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:

[[Image: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:

[[Image: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 ==

=== Make a graph ===

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:

[[Image:Least_squares_excell_insert_graph.PNG]]

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

[[Image:Least_squares_excell_graph_type.PNG]]

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

[[Image:Least_squares_excell_graph_preview.PNG]]

On finishing, our graph should look like this:

[[Image:Least_squares_excell_graph.PNG]]

=== Calculate parameters ===

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:

[[Image:Least_squares_excell_graph_add_line.PNG]]

Make sure the selected type of fit is linear:

[[Image: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":

[[Image: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:

[[Image:Least_squares_excell_graph_equation.PNG]]

[[Category:Statistics]]
[[Category:Resources last modified in May 2012]]
[[Category:Microsoft Office/Excel]]