Has anyone seen this before? If so, how do I work around it?
I've been quite happy using both linest and the trendline functions. However, I've found a very odd behavior when forcing the intercept to zero.
I'm currently fitting a second order polynomial (with and without the zero intercept). Here is what I'm currently seeing when using an intercept of zero (FALSE for "const" in LINEST):
Excel 2007:
The second order term in the trendline equation is incorrect -- it's the same coefficient as the non-zero intercept case -- though LINEST appears to come up with the proper coefficient. However, LINEST comes up with the wrong R^2 term (it shows a higher R^2 with a forced zero intercept).
(A side issue with Excel 2007 seems to be when showing the equation for a zero intercept case, the second order coefficient will often disappear which I suppose isn't so bad since that coefficient is incorrect anyway...)
Excel 2003:
The trendline coefficient on the graph are correct but the LINEST R^2 is still incorrect.
Thank you.
I've been quite happy using both linest and the trendline functions. However, I've found a very odd behavior when forcing the intercept to zero.
I'm currently fitting a second order polynomial (with and without the zero intercept). Here is what I'm currently seeing when using an intercept of zero (FALSE for "const" in LINEST):
Excel 2007:
The second order term in the trendline equation is incorrect -- it's the same coefficient as the non-zero intercept case -- though LINEST appears to come up with the proper coefficient. However, LINEST comes up with the wrong R^2 term (it shows a higher R^2 with a forced zero intercept).
(A side issue with Excel 2007 seems to be when showing the equation for a zero intercept case, the second order coefficient will often disappear which I suppose isn't so bad since that coefficient is incorrect anyway...)
Excel 2003:
The trendline coefficient on the graph are correct but the LINEST R^2 is still incorrect.
Thank you.
