ufadventure.blogg.se

\(Y2\) and \(Y3\) have the same slope as the line of best fit. On clicking "Accept", Excel will calculate a Least Squares fit, show the formula of the line obtained, and plot the line.= -173.5 + 4.83x\) is the line of best fit. 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": Make sure the selected type of fit is linear: First we select the points on our graph (by clicking on one of them) and select "Add tendency line" in their context menu: Now we are ready to tell Excel to calculate a Least Squares fit. On finishing, our graph should look like this:Ĭalculate parameters We can preview the graph to be sure that no incorrect values were selected: We select the graph type "XY (Dispersion)":

To do this, select all the x and y values (care not to select the sums) and click on: To make Excel calculate directly the parameters of the least squares fit, we must first make a graph of the points. (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 When we have the sums, we calculate a and b using these values: (Note Cell B6 should say -7 not -6, if you look at the chart below it plots -7.) We then instruct Excel to sum these columns:

Excel is smart enough to adjust the formula so that each value is calculated correctly in each row.

We must copy the formula to the other cells of the column. This is the way to make Excel calculate those two columns: We introduce our data in columns, and add columns for x i 2. "Manual" calculation of the parameters įirst, as said, we will make Excel help us in the calculation of a and b.