To use Excel to fit an equation by Linear Least Squares Regression:
Y = A + BX + CX^2 + DX^3 + ...Have your Y values in a vertical column (column B), the X values in the next column to the right (column C), the X^2 values to the right of the X values (column D), etc.
Below this block of numbers select a block just as wide as that one and 2 to 4 rows high - so if you just have Y and X values (linear equation) the new highlighted block is 2 columns by 2 to 4 rows.
Type "= linest(" and then drag down to hilite your column of Y-values - this should now look like "= linest(B2:B12" .
Type a comma, then drag across all of the X, X^2, X^3, ... values from upper left to lower right to hilite the whole block - this is just one column if you have a linear equation - and now the text should look like "= linest(B2:B12,C2:C12" or "= linest(B2:B12,C2:E12" if you have cubic terms.
Now type ",1,1)" and hold down the open-apple key (next to the space bar) while you hit Enter. If you type "0" in place of the first "1" it forces A to be zero. "0" in place of the second "1" cuts off uncertainties in A, B, C, ... Dragging across the values to include them in the formula is just a convenience, you can simply type the "linest" statement if you wish.
The equation that you are fitting does not have to be
a power series.
You could be fitting Y to any set of variables (X,V,W):
Y = A + BX + CV + DW.Just put your X- values in column C, V-values in column D, and W-values in column E
In the new block, the top row is the coefficients
in reverse order - the one on the far right is A, then B to the left
of that, etc. Below each of these is an estimated uncertainty
or standard deviation in the value. The leftmost entry in the
third row is the correlation coefficient (R^2)
which should be close to 1.000. The second item is the standard
deviation (the scatter) of the Y values about the calculated
The fourth line is other statistical things.
|___B____|___C___|____D____|__ E ___|___F___|2 |___Y1___|___X1__|__X1^2__|__X1^3_|__
Hilite this whole block by dragging the mouse from cell B8 to cell E11
Type "= linest(B2:B7,C2:E7,1,1)" and "open-apple" ENTER
Gives Y = A + BX + CX^2 + DX^3
dF = degrees of Freedom = (# of Y-values) - (# of parameters)The # of Y-values should be at least the square of the number of parameters.
1. Select data to be graphed, usually in (a) column(s). The name of the column for the Legend may be included as the topmost entry. If only one column is selected , that is normally the Y-values, and the X-values are equally spaced (1,2,3,...). If two are more columns are selected, you will be able to designate one as the X-values - the other columns will be graphed on different lines. It is best to have the X-column on the left of the Y-column(s). It is OK if some Y-values are missing from some of the columns.
2. Drag down under INSERT on the menubar to CHART. Select whether you want the chart to be on the same page as the data or on a different page. You can Cut&Paste later, so this isn't critical.
3. Hold down the mousebutton and drag over the area where the chart will appear. You can reshape and relocate it later if you wish. A dialog box appears.
4. Check that the indicated range is the data that you want to graph. Click on NEXT.
5. Choose a SCATTER Plot or a LINE Plot (IF you haven't entered X-values). Click on NEXT.
6. Choose the format that you want for the graph. Click on NEXT.
7. Check the entries that appear, then click on FINISH.
1. Click on the graph to select it for moving or re-shaping. Double-click to produce a thick border in order to make internal changes.
2. Drag down under FORMAT to CHART TYPE to change the basic type of plot (as in 5 above). Drag to AUTOFORMAT to change the Format (as in 6 above). Drag to OBJECT to change fonts, colors, line types, etc.
3. Double-click on an axis to edit it. You can set the scale, tick marks, font, etc. This is also where you choose symbols, line colors, dashed lines, etc.
4. Click on the CHART WIZARD icon on the ToolBar to change the data selection and other attributes.