Solved – the value of “X” in a regression equation when dealing with a time series

excelpolynomialregressionregression coefficients

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

Best Answer

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.

Related Solutions

Solved – Problems with modeling a cumulative dependant variable

Using a squared term to capture a curvilinear relationship in your data is fine, but it's worth noting that this can only capture one exact shape of curvature. This can be very limiting in practice. What you want typically is to use smoothing splines. I wrote about this here: What are the advantages / disadvantages of using splines, smoothed splines, and Gaussian process emulators?, which may be helpful to read. However, these techniques are somewhat advanced, and I doubt they can be done in Excel. Moreover, extrapolation is very hard to do well (here's a humorous example), and extrapolation is especially likely to go awry when using splines. Most likely, you will need to work with a statistical consultant.

Solved – How to calculate the confidence interval of a function of a combination of two linear models

(All credit goes to Robert Shriver, I'm just posting this for him!) This is not an analytic solution, but I think that out of all the iterative solutions this ones' the nicest:

Take the mean regression parameters (intercept and slope) for both regressions (f1 and f2) along with the variance-covariance matrix of the intercept and slope parameters for each regression and plug them into a Multivariate normal distribution to simulate some new intercepts and slopes.

nn <- 100000
nx <- 100

bootf1 <- mvrnorm(nn, f1$coefficients, vcov(f1))
bootf2 <- mvrnorm(nn, f2$coefficients, vcov(f2))

This will be 10,000 random draws of the slope and intercept parameters for each regression. Use each one of these to determine the mean value of z across the range of x values you are interest in.

f1a <- bootf1[,2]
f1b <- bootf1[,1]
f2a <- bootf2[,2]
f2b <- bootf2[,1]

f1a <- rep(f1a, 1, nx)
f1b <- rep(f1b, 1, nx)
f2a <- rep(f2a, 1, nx)
f2b <- rep(f2b, 1, nx)

x  <- seq(-100, 1100, length=nx)
xs <- matrix(rep(x,nn), ncol=nx)

f1y <- f1b+f1a*xs
f2y <- f2b+f2a*xs

z = 1 / exp( 0.02254*f1y^2 / f2y^2 )

Then you can find the lower and upper CI of z as well as its mean by finding the .025 and .975 quantile of z at each x value.

setquantile <- function(x){
  r <- quantile(x, probs=c(0.025, 0.975))
  return(r)
}

ci <- apply(z, 2, setquantile)
mu <- apply(z, 2, mean)

plot(x, mu, type="l")
lines(x, ci[1,])
lines(x, ci[2,])

That's it. Please let me know if you find something better. Hope this helped someone!

Best Answer

Related Solutions

Solved – Problems with modeling a cumulative dependant variable

Solved – How to calculate the confidence interval of a function of a combination of two linear models

Related Question