Posted by:

## Find the equation of a line on Excel – By KulfiSoftware

Excel has four ways to find the equation of a line on excel: LINEST (or LONGEST) function, trendline on a chart, regression analysis in the Data…Analysis ToolPak, and Solver. Each of these methods uses the least squares method to find the coefficients of the equation.

I suggest looking at the R-squared statistic when you do this to make sure the equation is explaining most of the variation in your data. R-squared varies between 0 and 1, with 0 meaning your data are completely random and 1 meaning the equation completely explains all the variation. For scientific and engineering purposes, I like to see R-squared over 0.9.

LINEST

Most of the people normally use LINEST. If you have an unknown variable, it returns a 5 row by n+1 column range of results, with the coefficients in reverse order in the first row (i.e. constant on the far right). The fourth row contains the R-squared statistic. LINEST is designed for linear equations, but it works quite well with polynomials, too.

If you have y values in column A and x values in column B, you can find the coefficients of a cubic polynomial by selecting a 5 row by 4 column range of cells and array-entering a formula like shown below. Note the use of the array constant in the curly braces to avoid the need for three columns of x values (one for x, one for x squared, and one for x cubed).

• =LINEST(A2:A30,B2:B30^{3,2,1},TRUE,TRUE)

Array-entering means to hold the Control and Shift keys down, then hitting Enter. Excel will respond by adding curly braces { } surrounding your formula and populating each cell in the 5×4 range of cells with different values.

Trendlines on a scatter chart

Plot your data on a scatter chart, right-click any of the data points, then choose Trendline from the resulting dialog. You may choose from several different types of trend lines, including linear, polynomial, exponential and logarithmic. I like to check the boxes to display both the equation and Rt squared statistic.

The equation on the chart frequently doesn’t display enough significant figures. You can increase the number of decimal places by selecting the equation and then using the Chart Tools…Format…Format Selection menu item to display a taskbar with formatting options. Click the column chart icon on the taskbar, then change the number format category to Number and specify the number of decimal places.

Analysis tool pak

Make sure the Analysis tool pak add-on is loaded by looking for the data a data Analysis menu item. If you don’t see it on the far right of the Data ribbon, use the developers excel Add-Ons menu item to check its box and load it. Don’t select the Analysis tool pak – VBA, as that is something different.

Once loaded, use the data…data Analysis…Regression menu item to select your data and tell where to display the results.