How do you find the equation of a line on Excel if you only have the data points?

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.

find the equation of a line on Excel

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.

find the equation of a line on Excel

Solver Add-In

The Solver add-in is the most general method of getting regression coefficients and can fit any kind of equation that you can imagine. You don’t get R-squared values, just the coefficients.

Make sure the Solver add-in is loaded. You should see it on the Data ribbon on the far right. If not, check its box in the Developer…Excel Add-ins menu item.

To set the problem up for Solver, you will need to list your x and y data in columns along with a column for the calculated y values. These calculated y values should reference cells containing guesses for the regression coefficients. The solver will then vary these guesses to minimize the sum of the squares of the difference between actual y and calculated y. I like to use the SUMSQ function to add up these squares in an array-entered formula like:

  • =SUMSQ(ActualYvalues CalculatedYvalues)

Select the cell with the SUMSQ formula. Open the Data…Solver menu item, choose to Minimize and specify the cells with the guesses for the coefficients as the Changing cells. The solver will now vary the coefficients to find the least squares fit.

Read more useful post/tricks/tips about excel by just clicking here.

0

Add a Comment