Line of Best fit in EXCEL ?

JamesJames

I have plotted a few points (8 infact ) in Excel and would like to acquire the equation for the line of best fit. How do I do this using the computer or do I have to do it by hand?

Dr Transport

You can either do a "least squares" fit to what ever order of polynomial you want to use. In Excel, plot the data points, then you can fit the data points to any order of polynomial up to 6th order, exponential curve and a few more I ccannot remember. The correleation coefficient is diplayed on the polt, so you can determine the best equation from that.

russ_watters

Adding a trend line is easy through the chart properties, but fitting an equation is a little tougher (unless you do it manually by looking at the fitted trend-line). IIRC, it requries an add-in that is not installed automatically. In the tools menu, click "add-ins" and I think the "Solver add-in" is the one you need.

robphy

In Excel, when viewing an XY-scatter chart look for the "Chart" menu and select "Add Trendline". To get the equation of the line, you have check the box in the Options tab of "Add Trendline".

Integral

The excel command you are looking for is

LINEST(yrange,xrange, b, stats)

for the x and y ranges enter the row column information for your data for example if your data is in the first 8 cells of the first 2 columns you would enter a1:a8 for the xrange and b1:b8 for the y range. The b and stats parameters are logical (b=true if you want Excel to compute the y intercept, b=false of you want it to be 0) The last gives more stats if you set it to true.

You need to select a 3x2 block of cells type = in the command line, enter the formula then type ctrl+Shift+enter to enter the Array formula.

You would do well to get into the Excel help system and research array formulas and the Linest command.