X-axis months, trend eq doesnot give the right answer, -ve values

[Guest]

I plotted months vs numbers in excel and got a trend line with an equation.
When I try to put my xaxis (months) in the trend equation I am getting
strange results. -ve results why so? If I replace months with numbers then I
get the right answere.

Secondly, my graph appears to fit best with the moving average trend but I
cann't see the R2 value and the equation for future predictions.

What is the easiest way to predict future using trend lines or other
features of Excel

 

[Jon Peltier]

Are you using a line chart? If the categories are just "months", like "Jan",
"Feb", etc., Excel is not using a numerical scale corresponding to the
number of elapsed months, but merely counting numbers 1, 2, 3, etc. To
illustrate, if I have

Jan, Feb, April

this is elapsed months 1, 2, 4

but in a line chart, Excel treats it as 1, 2, 3; in other words the items in
a list.

If you put in the numbers 1, 2, 4 etc., you will get a better fitted line.
To get a better fit to a time series, use real dates, like 1-Jan-2006,
1-Feb-2006 for the months, and format to show just the month name. Any gaps
are understood by Excel, and the line chart uses (or can be made to use) a
time scale, which plots dates according to elapsed time between, not
according to their placement in the list.

A moving average by definition has no R-squared, and no prediction
functionality, since it only looks back N periods and averages the values.

- Jon