Get value equal to final point of chart trendline [=Trend( )] ?

[ker_01]

I don't use trendlines much, and when I do it is just for the visual.

Now I find myself in a situation where I need to plot the trendline, but
also take the endpoints of two trendlines on my graph and get the difference
in value between those final points. The graph X-axis is 12 months (Jan-Dec)
and the actual number of points plotted will vary (adding a point each month).

The graph uses the values based on a named range: =opov4.xls!vo12 (fixed
range, not dynamic). It also uses a named range for the x-axis values:
=opov4.xls!vXAxisShort

I can put [vo12] in as the first parameter of the trend function
successfully (it shows the array of values), [vXAxisShort] for the X-axis
values... but no matter what I put in to try to convince it to give me the
value for "Dec" (or 12), it doesn't return a value without any x values.

I also tried the same for the "Forecast" function, and still no love.

The help files for trend and forecase aren't all that helpful to understand
why it isn't working.

Suggestions are welcome!
Thank you,
Keith

 

[Bernie Deitrick]

Keith,

You should try FORECAST. From your description, it should be something like

=FORECAST(12,vXAxisShort,vo12)

But note that your vXAxisShort values need to be of the same 'type' (integers for months) as the
12 - so you can't just have, for example, formatted date cells as "m" to justs show the month and
have that work - in that case, use

=FORECAST(DATEVALUE("12/31/2009"),vXAxisShort,vo12)

HTH,
Bernie
MS Excel MVP