TREND and FORECAST not working as expected?

Hi All,

I'm having trouble with TREND and FORECAST. I have a column with population values by month, which I want to extend out. I'm using TREND, but it spikes up before continuing what was otherwise a 'smooth' trend.

Check out the spreadsheet attached and chart showing the spike.

The actual data looks logarathmic... is there a way to get excel to trend it that way?

The TREND function is applying a line to the data,
so theres going to be a bump because there is a
bend in the data. I returned your file with the
prediction using the data only back to the bend
and you can see it straightens it out a little. You
might try using some kind of algorithm to extend
the data other than what the chart offers. I'm not
familiar with what excel has to offer but there's
probably some binomial or other kind of forecaster
that will approximate a curve.

attachment should show up this time.

The actual data looks logarathmic... is there a way to get excel to trend it that way?

Excel's regression functions will work on any "linear" function. The key skill is to coax the function to look linear. I'm guessing by "logarithmic" you mean a function that looks like p=A+B*log(t), which is a linear function in log(t). (If needed, you can do the substitution to see this: let x=log(t), then substitute into the equation, which becomes p=A+B*x, which should clearly show that p is a linear function of x.)

In setting this up in the spreadsheet, I would add a column near the raw data =LOG(t) (or =LN(t) if I'm more in the mood for natural logarithms). Then use this column as the "known_x's" parameter in the TREND/FORECAST/LINEST function. Recognize that, because "known_x" is log(t), you have to use log(t) for the "new_x" parameter, too.