Polynomial Fit in EXCEL does not work as it should

In summary, the conversation discusses how to extract 2nd degree polynomial coefficients from a curve of best fit in EXCEL using the LINEST function. The equation and formulas necessary for this are provided, but the individual is having trouble getting accurate results and wonders if they are using the method correctly. Another person suggests double-checking which columns represent Y and X in the data set.
  • #1
Woland
18
0
Hello all,

I am trying to extract the 2nd degree polynomial coefficients from a curve of best fit applied in EXCEL. I know how to do it (http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas)

2nd Order Polynomial Trendline

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)


Iv done it before, but for some reason it is not working out for my particular data set. I keep getting errors or zero for my coefficients. If I do the regular best fit using the trendline option, it works.

I get: y = 2E+16x2 + 1E+11x + 3.033

I am attaching my data set.

Does anyone know if there is something wrong with my method? Perhaps I am forgetting something.

Thanks.
 

Attachments

  • data.txt
    1.4 KB · Views: 347
Physics news on Phys.org
  • #2
You should pay attention to of the two columns represent Y and which represents X. I think you are reversing them between the trendline and the LINEST function.
 

Related to Polynomial Fit in EXCEL does not work as it should

1. Why is my polynomial fit in Excel not giving accurate results?

There could be several reasons why your polynomial fit in Excel is not giving accurate results. Some possible reasons include:

  • Incorrect data input: Make sure the data you are using for the polynomial fit is accurate and complete. Even a small mistake in data entry can significantly affect the results.
  • Insufficient data points: For a polynomial fit to be accurate, you need to have a sufficient number of data points. If you have too few data points, the fit may not accurately represent the trend in the data.
  • Incorrect polynomial degree: The degree of the polynomial used for the fit may not be appropriate for the data. Make sure you select the correct degree for your data set.
  • Outliers in the data: Outliers, or data points that significantly deviate from the rest of the data, can greatly affect the results of a polynomial fit. It is important to identify and remove any outliers before performing the fit.

2. How do I perform a polynomial fit in Excel?

To perform a polynomial fit in Excel, follow these steps:

  1. Enter your data points in two columns, with the x-values in one column and the corresponding y-values in the other.
  2. Select the data points and click on the "Insert" tab.
  3. In the "Charts" section, click on "Scatter" and then select "Scatter with Straight Lines and Markers" from the drop-down menu.
  4. Right-click on any data point in the chart and select "Add Trendline."
  5. In the "Format Trendline" window, select "Polynomial" as the "Type."
  6. Adjust the "Order" to the desired degree of the polynomial.
  7. Check the box next to "Display Equation on Chart" to show the equation for the polynomial fit.
  8. Click "Close" to see the polynomial fit on the chart.

3. Why is my polynomial fit in Excel giving a different equation than other software?

Different software programs may use different algorithms and methods to perform a polynomial fit. This can result in slightly different equations for the same data set. It is important to understand the differences in the methods used and make sure you are using the most appropriate method for your data.

4. Can I perform a polynomial fit on non-linear data in Excel?

Yes, Excel allows you to perform a polynomial fit on non-linear data. However, keep in mind that a polynomial fit may not accurately represent the trend in non-linear data. In such cases, it may be more appropriate to use a different type of regression analysis.

5. How can I improve the accuracy of my polynomial fit in Excel?

To improve the accuracy of your polynomial fit in Excel, you can try the following:

  • Increase the number of data points: Adding more data points can help improve the accuracy of the fit.
  • Use a higher degree polynomial: If your data is highly non-linear, a higher degree polynomial may better represent the trend in the data.
  • Remove outliers: Outliers can significantly affect the accuracy of a polynomial fit. Try removing any outliers from your data and see if it improves the fit.
  • Try a different regression analysis: If a polynomial fit is not accurately representing the trend in your data, you may want to try a different type of regression analysis, such as exponential or logarithmic.

Similar threads

I Error bars in Excel (standard deviation, standard error, percentage)
    • Set Theory, Logic, Probability, Statistics
Replies
4
Views
974
A How to perform a fit with correlated variables
    • Set Theory, Logic, Probability, Statistics
Replies
16
Views
2K
How can I accurately set up a tri-quadratic equation for curve fitting in Excel?
    • Set Theory, Logic, Probability, Statistics
Replies
1
Views
4K
I Exploring Nonlinear Least Squares for Regression Analysis
    • Set Theory, Logic, Probability, Statistics
Replies
4
Views
1K
I Which method should I use for a linear fit of data with error on both z and y?
    • Set Theory, Logic, Probability, Statistics
Replies
8
Views
922
Uncertainties and LINEST in Excel....
    • Set Theory, Logic, Probability, Statistics
Replies
1
Views
1K
I Plot Linear Fit w/ Error Bounds: y=ax+b
    • Set Theory, Logic, Probability, Statistics
Replies
3
Views
1K
I Weird covariance matrix for linear fit
    • Set Theory, Logic, Probability, Statistics
Replies
1
Views
851
I Should I Remove the Outlier Point in My Linear Fit?
    • Set Theory, Logic, Probability, Statistics
Replies
16
Views
1K
I What are the commonly used estimators in regression models?
    • Set Theory, Logic, Probability, Statistics
Replies
23
Views
2K

Hot Threads

Recent Insights

Back
Top