Stock

LINEST in Excel The Excel spreadsheet function "linest" is a complete linear least squares curve fitting routine that produces uncertainty estimates for the fit values. There are two ways to access the "linest" functionality; through the function directly and through the "analysis tools" set of macros. These instructions cover using "linest" as a spreadsheet function. Using linest in your spreadsheets is very easy, after you master the concept of an array function. Array functions are functions that while entered into a single spreadsheet cell produce results that fill several cells. The steps outlined below take you set-by-step through the process of linear curve fitting.

Step 1. Type in your data in two columns, one for the x variables and one for the y. You can use any labels you would like. "x" and "y" are used in the example at right for convenience.

Step 2. Select the area that will hold the output of the array formula. For "linest" you should drag to form a 5 row by 2 column data array.

Step 3. Click in the formula bar at the top of the screen. Now press the function wizard button. This button is in the formula bar and is labeled "fx". A two-part scroll box will appear; in the left scroll box click on "Statistical" and in the right click on "LINEST". Next click on "Next>." The window shown below will appear. On your spreadsheet select the cells containing the y values by

dragging in the original spreadsheet using the mouse. Click in the "known_x's" dialog box, and select the cells containg the x values. Type in "TRUE" in the last two dialog boxes. The first TRUE indicates that you wish the line to be in the form y=mx+b with a non-zero intercept. The second TRUE specifies that you wish the error estimates to be listed. The Function Wizard dialog box should then appear as below.

Step 4. Click on "Finish." The formula bar should then appear as below, although your y and x cell ranges may be different, of course. If the values are incorrect, you...