It is OK if some Y-values are missing from someof the columns.Ģ. It is best to have the X-column on the leftof the Y-column(s). If two are more columns are selected,you will be able to designate one as the X-values - the other columns willbe graphed on different lines. 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 theX-values are equally spaced (1,2,3.). Type " = linest(B2:B7,C2:E7,1,1)"and " open-apple" ENTERġ1 |_?_|_dF_|_|_| dF = degrees of Freedom = (# of Y-values) - (#of parameters) The # of Y-values should be at least the square ofthe number of parameters.ġ. Hilite this whole block by dragging the mouse from cell B8 to cell E11 The fourth line is other statistical things. The second item is the standarddeviation (the scatter) of the Y values about the calculatedline. The leftmost entry in thethird row is the correlation coefficient ( R^2)which should be close to 1.000.
Below each of these is an estimated uncertaintyor standard deviation in the value. In the new block, the top row is the coefficientsin reverse order - the one on the far right is A, then B to the leftof that, etc. Just put your X- valuesin column C, V-values in column D,and W-values in column E You could be fitting Yto any set of variables ( X,V,W): Y = A + BX + CV + DW. The equation that you are fitting does not have to bea power series. Dragging across the values to include them in theformula is just a convenience, you can simply type the "linest" statementif you wish. " 0" in place ofthe second " 1" cuts off uncertaintiesin A, B, C. If you type " 0"in place of the first " 1" it forcesA to be zero. Now type " ,1,1)" and holddown the open-apple key (next to the space bar) while you hit Enter. values from upper left to lower right tohilite the whole block - this is just one column if you have a linearequation - and now the text should look like " = linest(B2:B12,C2:C12" or " = linest(B2:B12,C2:E12"if you have cubic terms. Type a comma, then drag across all of the X,X^2, X^3. Type " = linest(" andthen drag down to hilite your column of Y-values- this should now look like " = linest(B2:B12". Have your Y values ina vertical column (column B), the Xvalues in the next column to the right (column C), the X^2values to the right of the X values (column D), etc.īelow this block of numbers select a block just as wideas that one and 2 to 4 rows high - so if you just have Yand X values (linear equation)the new highlighted block is 2 columns by 2 to 4 rows. To use Excel to fit an equation by LinearLeast Squares Regression: Y = A + BX + CX^2+ DX^3 +.
0 kommentar(er)
