Regression made with LINEST not giving correct value

[timtim89]

Hi all,

My problem is that I have a number of countries for which I have a couple of observations (y,x) on which I want to make a 3th degree polynomium regression. I use the function LINEST and can doublecheck the results by plotting in the observations in a graph and ask for the build-in application to do the regression. I most cases my result is the same as that regression, but for some reason that I cannot figure out, one (Country7) gives me a completely different regression. Want I get using LINEST is obviously not in acordance with the observation. Please take a look at the attachment.

I would be very happy if someone could figure out, what is wrong.

Thanks in advance.

Timo

[MrShorty]

The regression itself looks correct to me. I think where you are going wrong is in creating the line chart. If you look at the known_x's that go into each regression (columns Q-U), you will notice that the known_x's for country 7 are different from the known_x's for country 1. However, in rows 16 and 17 where you are putting the data for the line chart data series, you are plotting country 7's known_y's against the same known_x's as country 1. I think your problem will be fixed if you adjust the placement of country 7's known_y values in row 17 so that they correspond to the actual known_x's for country 7 (7,000 should go in D17 and so on).

[timtim89]

Ah that did the trick! Matching the values as you suggested. Perfect, thanks a lot