not only are the x values large, the difference between them and the y values is huge. My data points:

















The only nice pair there is the first one, and it MUST be produced by the equation, whatever it is. I've tried Wolfram Alpha and Excel to plot them and create a regression line, but neither of them can handle the large numbers or something. Wolfram just says it can't do anything with it, and Excel only generates a binomial equation (even when I select a higher order one) that isn't anywhere near correct. Is there any way to do this?

  • $\begingroup$ The absolute size of the x-coordinates is irrelevant to the algorithm. You can divide them by 1000000 if that helps, and then multiply the result by 1000000. (Formally: let X = x/1000000; solve for X; and then substitute x/1000000 for X in the result.) But I doubt that this is the reason for your problem. $\endgroup$ – TonyK Jan 23 '14 at 16:09
  • $\begingroup$ The shape of your data in the pictures in the answers suggests that you should use a logistic model rather than a polynomial. See en.wikipedia.org/wiki/Logistic_regression . I don't know about how to do this in Excel but I'm sure the web can tell you. $\endgroup$ – Ethan Bolker Mar 13 '17 at 19:42

If I correctly understand, you want to fit a polynomial model such as

$$Y = a_0 + a_1 X + a_2 X^2 + a_3 X^3 + \cdots$$

but you want that the first point $(X_0,Y_0)$ be exactly matched. So, you have a parameter which has to be removed. Rewrite your equation as

$$Y - Y_0 = a_1 (X - X_0) + a_2 (X - X_0)^2 + a_3 (X - X_0)^3 +\cdots$$

You see that here, your first point is perfectly matched. So, define as new variables

$Z_i = Y_i - Y_0$
$T_i = X_i - X_0 $

and perform your regression as

$$Z = a_1 T + a_2 T^2 + a_3 T^3 + \cdots$$

But do not forget to exclude the intercept (the option "nointercept" is available in almost any regression tool). If you do not have this capability, let me know.


If the first point must be on the line exactly, that eliminates one degree of freedom from the standard fits. The large numbers are not a problem-there sometimes is if they are over a small range. I got Excel to do a third order polynomial fit, but it doesn't fit very well. A fifth order fits decently by eye. If you want to extract the coefficients, it would help to scale your first column by dividing by $10^5$ or so. I have done so in the below image. For a "rough and ready approach" I would take this fit and add the correct constant to make the first point fit. One way to force it closer to the first point is just to duplicate the first point a bunch of times. The image has $18$ copies of the first pointenter image description here

  1. There are valid reasons to force the curve through a point. There are also reasons not to do so. The point of least squares is to arbitrate errors. Forcing the curve through a point forces error increases error at other points.

  2. Thinks about rescaling the data as done here. The $x$ variation is less than an order of magnitude; the $y$ data variation less than 4 orders of magnitude.

  3. The optimum degree of fit requires so thought. The rescaled data was fit with polynomials from order 0 (a constant, the average) to order $d=12$. A summary plot follows.

Total error by order

As the order increases, the error decreases. The total error may achieve a minimum, or, as in this case, flatten out. You will see that lower errors may mean the basic shape of the curve is corrupted.

d=0 d=1 d=2 d=3 d=4 d=5 d=7 d=8 d=9 d=10 d=11 d=12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.