Producing a Quartic from points

[mrharriss]

Hi Everyone,

for a maths assignment, I had to model an equation based off of points in an equation. I have to model both a quadratic equation and a quartic equation.

The issue is here, I was able to successfully produce a quadratic equation through excel by plotting the points on a scatter plot, inserting a polynomial and changing the Order to 2.

I repeated this process for the quartic, but with the order to 4.

This produced the following equation:
y = -3E-08x4 + 1E-05x3 - 0.0081x2 + 1.5906x + 0.3084
I substituted x values that i had already obtained to determine the y value at the certain x value, it turned out the equation produced a completely different curve to what it should of.

Excel says the quartic has an R^2 value of 0.993 and the R^2 value of the quadratic formula is 0.983, which is a much more innacurate curve.

Why does it have a higher R^2 value, where it only intercepts half of the points?


For more context please see the attachment

[MrShorty]

Based solely on the picture, I cannot say why your quartic is not better. As a guess, the most common mistake people make in these cases is assuming that the coefficients reported by the chart trendline are exact (for example, the first term is exactly -3.0000000000E-8x^4). Of course, for convenience, Excel is only showing the paremeters to one or 2 significant figures, but the exact calculation is a 15 digit number. Try formatting the trendline equation to show more digits (maybe a format of 0.0000000E+0) to make sure you are getting more precision when you transfer the coefficients into the spreadsheet.

If this is something you are going to do frequently, you may find that it is preferable to perform you polynomial regressions directly in the spreadsheet using the LINEST() function. Help file -- note the cubic polynomial example given towards the end of the "Remarks" section: https://support.office.com/en-us/art...a-fa7abf772b6d

If your problem to be something different please describe in more detail exactly how you are calculating the plotted values for the quartic equation.