1
$\begingroup$

I am using excel to add a polynomial trend line to a chart. The chart and the formula of the trend line are shown below. I want to add lines indicating different confidence intervals so I need to find out the y values representing the trend line drawn on the chart.

If I had 2 columns of "normal" data - e.g. height and weight - I understand that I could just plug in the x value into the equation and get the result of the trend line. However, in this case my x axis is a series of dates. What is the value of x that you would plug into the equation to generate that line? I can't plug in a date (or can I?) and if I play with the formatting, excel turns the dates into very large numbers (my guess is number of days since a certain start date). What I am missing? Is there an easier way to do this?

Thank you

Picture of chart with formula

Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ Confidence intervals for such a fit would be meaningless: those data certainly don't follow such a trend and it's not even a good qualitative description. $\endgroup$
    – whuber ♦
    Nov 22, 2014 at 1:25
  • $\begingroup$ @whuber Ok I just wanted to see if I could calculate them. Can you suggest a better method? I don't have much experience outside of a normal linear regression and I normally use R not excel. This just don't look linear at all. $\endgroup$
    – Rarw
    Nov 22, 2014 at 15:56

1 Answer 1

Reset to default
2
$\begingroup$

The value of x used to generate any point on that trend line is indeed the "very big number" that is the way Excel actually stores dates. It is - as you partially surmised - the number of days since Jan. 1, 1900 - so for any remotely current dates, the number is pretty large. If you are keeping not just dates, but dates and times - the time portion is kept as a fraction of a day (so 12 noon on a particular day is stored with a number 0.5 larger than the preceding midnight).

You will also want to be careful to stick just to the region of your data - with a 6th order polynomial - if you move very far outside the range between 2005 and 2012 - the values of the trend line are quite likely to become quite extreme (I think, looking at your coefficients, that if you tried to plot a value for a date in 1970 - or 2050 - it would be incredibly huge, but I might have things backwards, and those values could be extremely negative numbers).

Edited to add - User777 below is correct. Excel stores dates so that Jan. 1, 1900 is 1 - so it stores a number that is either the number of days since "Jan. 0, 1900" - or "Dec. 31, 1899" - whichever way is easier for you to understand.

Improve this answer
$\endgroup$
3
  • $\begingroup$ Oddly enough, Excel counts days since Jan 0, 1900... It's very frustrating in that way. $\endgroup$
    – Sycorax ♦
    Nov 22, 2014 at 0:03
  • $\begingroup$ That makes sense. But then if I use the date value as X and plug it into the formula on the chart shouldn't I get the values for the yellow line (i.e. that should be the predicted y values)? That doesn't happen, which is why I thought excel might be using something different. $\endgroup$
    – Rarw
    Nov 22, 2014 at 15:58
  • $\begingroup$ I suspect that the reason you are not getting things to match up is because you need to display your coefficients to much greater precision (think 15 to 20 decimal places). See support.microsoft.com/kb/211967 for instructions on how to display more digits in the equation on your graph - or av8rdas.wordpress.com/2012/09/05/… for someone walking through the full rounding problems, and using an Array function in Excel to get exact coefficients $\endgroup$
    – Don Dresser LatentView
    Nov 24, 2014 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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