TREND LINES

[sfergus9]

Here is the data setup:

1) Two years, by month, of on-time delivery metrics (% on y-axis), separated by trade and intra-company sales orders (x-axis). Bar graph.


I want to draw a trend line, but I have gaps of missing data which should not affect the trend - how do I do it? More specifically, is there a way to only select certain data points in the trend analysis in Excel?


More information as example: I have all data from beginning of 2008 to present for all trade orders (drawing trend lines is no problem); however, data capture for intra-company orders began in 2009. So, when Excel draws the trend line for the data set with only current year data, it erroneously shows a positive trend since the first 12 months equal zero.

 

[keymaster]

Just curious, can you try to use NA() for missing values and then try the trend line. Ideally this should work. Not tested though...

 

[sfergus9]

So, in the cell, should it be =NA()? When adding these values in the cells, the R^2 value seems to be changing from about .78 to <.10. Intuitively, one would think that if the bars were relatively flat, the trend line values would be closer...or maybe the revised graph (using =NA()) should actually be better?