How can I regress a downward trending number series in Excel?

Question

I'd like to use these data to derive an equation using Excel.

300 13
310 12.6
320 12.2
330 11.8
340 11.4
350 11
360 10.8
370 10.6
380 10.4

As x goes up, y goes down. Seems straightforward. But when I do a polynomial regression on these data, even though the trendline matches the data pretty well, the equation it generates doesn't work. The equation is 0.0096x2 - 0.4181x + 13.341 When I plug in x values to that equation, the numbers go up! So something is pretty wrong here.

My steps:

• place both number series in excel
• select the second set (13, 12.6 ...)
• plot a line graph
• set the first set as the x axis labels
• select Series1 and add a polynomial (2) trendline, display equation, display R-squared

That produces the equation above, with an R^2 value of .9955. But when I use that equation, it doesn't produce those outputs for those inputs.

Clearly I'm doing something wrong.

Edit: or is it excel? Here's the graph of that equation (above):

Clearly that is not trending downward for the range 300-390.

Here's the real equation that fits this data:

Quadratic Fit: y=a+bx+cx^2 Coefficient Data:
a = 4.53E+01 b = -1.66E-01 c = 1.95E-04

Thank you CurveExpert 1.4.

Answer 1

I don't know how you got that equation in Excel as this is the equation that I get:

Check to make sure that the degree of polynomial is 2 and that you haven't set any intercepts or forecasts into the trend-line creation.

Another thing, you said, x increases and y decreases. This means that you should do an exponential curve fitting (Unless it's a linear decrease which it's not):

It's important to note that curve fitting really only works within the bounds of data that you've entered. You cannot "predict" accurately the future values using this. You can only estimate values in between the bounds of your original data.

The reason why I suggest using a exponential is due to an understanding of the "trend" of the data and how computers calculate these "trend equations". For example, let's say I have 3 data points, and create a polynomial function that fits perfectly with the data:

However as I take more data points, they lie out of what my original data points fit. (yes I understand that excel would NEVER make a function like this, but it's to emphasize a point) When analyzing data one must make some decision based on what they know.

Even though my R value is lower than yours (by a mere .01) knowing that the data decreases as x increase makes the exponential a better choice because of what you ALREADY know. Just as a linear fit would be the better choice in the graph above. This is the major difference between Extrapolation and Interpolation.

Answer 2

I entered these and got the result as

y = .000186x^2 - 0.160247x + 44.385628 with R^2 = 0.995

so a good fit

I entered x and y as column headers
Then first column was the x values and second column the y

Select both columns

Insert chart - fiddle axes

Add trendline.