Hi, I am trying to create a chart (the standard "Line" chart) in Excel from a set of 2 values, namely dates and number of items per date). I use the Chart Wizard from Excel and select the "Line" chart. I get a graph. So far so good. Then I add the Trendline. I can choose either a Linear regression type or a Polynomial regression type, among others. I also can display the R2 (also known as R-squared value or coefficient of determination). As you know, a trendline is most reliable when its R2 value is at or near 1. I choose first Linear trendline and also to "forecast" or extrapolate the series in 2 periods. I get a R2=0.0007 and the extrapolation is on the decrease. If I choose a polynomial regression trendline (order 2), the R2= 0.1695 (what means that the trendline it is more reliable than before), and also the trend is on the decrease. If I increase the order to 5, I get an R2 value = 0.3953 (even more reliable) and a trend on the decrease. However, if I increase the number of order to 6, I get a R2 value of 0.5849 (indicating that the trendline is even more reliable than before) BUT THE TREND is now ON THE INCREASE!!! What does that mean? The trend is on the decrease or on the increase? I am confused. I always thought that the trendline with the closer value to 1 should be chosen, but in this case I believe that the trend is on the decrease and not on the increase, as the last R2 value shows... Can anybody help? (I provide the set of values below).
Date; number of items
29.08.2008; 269
22.08.2008; 280
15.08.2008; 314
08.08.2008; 322
01.08.2008; 295
25.07.2008; 246
18.07.2008; 372
11.07.2008; 293
04.07.2008; 255
Tags: