How to Use LINEST on Excel

by Stephanie Ellen

Microsoft Excel is spreadsheet software that can help you manage large amounts of data. Excel is also a powerful mathematical program with built in functions that can help you with algebra, calculus and statistics. One useful Excel function is the least squares function, LINEST. The LINEST function is an advanced data analysis function that takes your available data and calculates the slope of the line y=mx+b using the least squares method.

1

Type your data into two columns in Excel. Place "x" values in one column, without spaces, and then place "y" values in another column, without spaces.

2

Click on the area where you want your output data to start and then drag the cursor to make a five row by two column area.

3

Click "Formulas" on the toolbar and click "Insert formula." Type "linest" into the search box and then click "Go."

4

Click "linest" from the results list then click "OK." This brings up the Function Arguments window.

5

Type the cell reference for your y values into the "Known_y's" text box. For example, if your data is in cells D2 through D4, type "D2:D4."

6

Type the cell reference for your y values into the "Known_y's" text box. For example, if your data is in cells E2 through E4, type "E2:E4."

7

Type "true" into the two remaining text boxes and click "OK." Excel will return your results in the selected area on the spreadsheet.

Tip

  • check If you are familiar with functions in Excel, you can skip the function keys and type the function directly into the output cell. The format is "LINEST(known_y's,known_x's,const,stats)."

Items you will need

About the Author

Stephanie Ellen teaches mathematics and statistics at the university and college level. She coauthored a statistics textbook published by Houghton-Mifflin. She has been writing professionally since 2008. Ellen holds a Bachelor of Science in health science from State University New York, a master's degree in math education from Jacksonville University and a Master of Arts in creative writing from National University.