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

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:

a = 4.53E+01 b = -1.66E-01 c = 1.95E-04

Thank you CurveExpert 1.4.

-
It is downwards trending if you look at the part of the drawn curve where x < 0. If you want it downwards for all values then try doing a linear fit. – Mark Feb 18 '11 at 11:11
If you put the X values into the equations and show 3 significant figures the y values for my solution and CurveExpert only differ for x=320 and mine is closer). Thus we need to use more significant figures and look at what the errors are in the solutions I have forgotten the full error analysis – Mark Feb 18 '11 at 12:25
@Mark: did you look at the graphs I posted? Those are never downward trending. The derivative of x^2 + c is always positive or zero. – jcollum Feb 24 '11 at 23:50
he derivative of x^2 + c is 2x so is negative for x< 0 – Mark Feb 25 '11 at 10:09
and is downwards on the left hand side - the curve fit is only showing a small part of the cuve – Mark Feb 25 '11 at 14:20

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.

-
You missed the part of my post where I put in the curve equation generated by CurveExpert. That equation was x^2 based and fit the data. Also, my original equation from Excel had a better R^2 value than the equation you generated. You're correct about the range, but I don't see the relevance. – jcollum Mar 1 '11 at 17:33
edited answer in response @jcollum – KronoS Mar 1 '11 at 17:46
OK. But this still doesn't get to the issue of why excel is a) saying that it's got an equation with a high R value that doesn't fit the data in any way. The equation generated by CurveExpert will actually generate the data in the range, while the equation generated by Excel will not. – jcollum Mar 1 '11 at 18:13
data decreases as x increase does not imply exponential at all - the choice of polynomial or exponential is the modle that underlies the data and is not necessarily chosen just from the data. I can construct parabolas where as x increases the y decreases - e.g. y = x^2 or y = -bx + c – Mark Mar 1 '11 at 23:37
@Mark right but in general and IMO, exponential is a better fit to the situation. – KronoS Mar 1 '11 at 23:43

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

@jcollum, `[link description](http://...)`, and also replace `^` with `%5E` – wilson Feb 18 '11 at 2:35