• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.



New Member
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.
Just curious, can you try to use NA() for missing values and then try the trend line. Ideally this should work. Not tested though...
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?