Formula that will give the equation for the line of best fit of a data set

[tburke2]

Is there a formula that will give me the equation for the line of best fit of a data set, the line being a 6th degree polynomial? I know how to graph the table and add a line of best fit while showing the equation but as far as I know the equation cannot be used in the given format in excel. If there was a way to refer back to the equation for further calculations that would work as well. Would it be easier to use a different program such as Matlab for in-dept data analysis? Thanks.

 

[collinsmark]

Hello tburke2,

Welcome to Physics Forums!

Is there a formula that will give me the equation for the line of best fit of a data set, the line being a 6th degree polynomial? I know how to graph the table and add a line of best fit while showing the equation but as far as I know the equation cannot be used in the given format in excel. If there was a way to refer back to the equation for further calculations that would work as well. Would it be easier to use a different program such as Matlab for in-dept data analysis? Thanks.

You might have luck with the Trendline, if that suits your needs.

• Start by inserting a chart/plot. It could be a Line chart if you are comfortable with your x-axis being the natural numbers. But you'll likely want to create a Scatter Chart instead, so you have control of the x-axis.
• Once the chart is made, left-click on any point. Now all points are highlighted.
• Now right click on a point, and a menu appears.
• Click "Add Trendline..." from the menu's options.
• Now off to the right, a new menu appears, "Format Trendline."
• Click the "Polynomial" in the radio-button choices.
• Select '6' in the "Order" menu (either type it in, or hit the up arrow repeatedly, until it displays '6').
• Click the checkbox that says "Display Equation on chart"

If you need to change any of the data, simply modify the original data and the equation will change accordingly.

On the other hand, if you need to add all new data points it might mean that you must re-select your data. So when originally selecting your data for the x- and y- axis, you might wish to select some unused elements for future data points.

 

[collinsmark]

Oh, wait. I think I understand what you would like to do.

You want to automatically grab the coefficients of the polynomial and insert those into cells in the spreadsheet for future use. Is that right? I'm not sure if that can be done automatically. If it can be done, I'm not sure how. [Edit: Directly, -- taken directly from the chart -- that is. See next post on how you might re-create the coefficients.]

But if you put the coefficients into cells manually, you can then use those to create a formula. You might find the built-in POWER() function useful along with the '+' and '*' operators.

Of course, if you ever change your original data (used to create the polynomial) you'll have to change the resulting coefficients manually in your spreadsheet. Again, I'm not sure how to link cells with the Trendline polynomial coefficients to spreadsheet cells automatically (if it can be done at all).

 

[collinsmark]

Oh, and something like this might help to re-create the coefficients.
http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

 

[rezeew]

Yes, there is a formula that can give you the equation for the line of best fit of a data set, even if the line is a 6th degree polynomial. This formula is known as the Polynomial Regression formula and it can be used to find the best fitting polynomial equation for a given set of data points. This formula takes into account all of the data points and finds the polynomial equation that best represents the trend of the data.

In Excel, you can use the "LINEST" function to find the coefficients of the polynomial equation and then use those coefficients to create the equation manually. However, if you are looking for a more advanced and in-depth analysis of your data, using a program like Matlab may be more beneficial. Matlab has built-in functions for polynomial regression and allows for more flexibility in data analysis and visualization.

Ultimately, the choice of program will depend on your specific needs and preferences. Both Excel and Matlab have their own advantages and can be used for data analysis, but Matlab may be better suited for more complex and detailed analysis.