Usually, the problem with Line Chart trendlines is that they use an ordinal number series, no matter what the x-axis values are.įor that reason, it is better to use a Scatter Chart with a Line subtype. In fact, I'm surprised that the Line Chart seems to have used the dates for its trendline. Although squaring them (B5:BQ5^2) is well within the limits of the internal binary representation that Excel uses, that might not be the case if you choose an order-4 polynomial or exponential trendline (for some other problem). Generally, it is better to replace the date range in the chart with either an ordinal number series (1st, 2nd, 3rd, etc month) or a series of cardinal number of days since the first date (with 1 for the first date).Įxcel dates are in the range 41640 to 44256 for your dates. Sigh, I cannot resist some editorial comments. The two data series (B6:BQ6 and B7:CJ7) are used to create "Calculated Line Chart" on the right. To calculate the data for the trendline, enter the following formula into B7 and copy in C7:CJ7: Select B2:D2 and array-enter (press ctrl+shift+Enter instead of just Enter) the following:Īlthough the coefficients are slightly different, they are the same up to 11 to 12 decimal places, which we can see when we format the trendline label to display Scientific with 14 decimal places. See the 'Timeline (2)' worksheet in the attached file.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |