Calculate Trendline With Dates in X Axis

[moandaa]

I need to know how to calculate the slope and intercept of a trendline for the following data:

3/20/2017	62.7%
4/10/2017	58.5%
4/17/2017	62.2%
5/1/2017	60.5%
5/8/2017	60.9%
5/15/2017	55.6%
5/29/2017	58.0%

<tbody>
</tbody><colgroup><col><col></colgroup>

Any help is appreciated.

 

[DRSteele]

Welcome to the forum.

Maybe you know this already, but I'll just mention it to be sure. The Dates are actually Excel Serial Numbers. If you remove the date formatting (by formatting them as General), you will see that 3/20/2017 is actually serial number 42814. This means that it is the 42814th day after Jan 1, 1900, which is serial number one.

So you can find the slope and intercept by including the Dates as the x-values and the Values as the y-values in functions SLOPE and INTERCEPT, like this. Copy A12 and B12 down as necessary.

If you create an XY Scatterplot and include the equation of the Trendline, you will see that the figures match.

	A	B	C	D	E
1	Date (formatted)	Value (formatted)
2	3/20/2017	62.70%
3	4/10/2017	58.50%
4	4/17/2017	62.20%
5	5/1/2017	60.50%
6	5/8/2017	60.90%
7	5/15/2017	55.60%
8	5/29/2017	58.00%
9
10
11	Date (unformatted)	Value (unformatted)
12	42814	0.627		intercept	30.3539
13	42835	0.585		slope	-0.00069
14	42842	0.622
15	42856	0.605
16	42863	0.609
17	42870	0.556
18	42884	0.580

<tbody>
</tbody>

Sheet49​

Worksheet Formulas Cell Formula A12 =A2 B12 =B2 E12 =INTERCEPT(B12:B18,A12:A18) E13 =SLOPE(B12:B18,A12:A18) <tbody> </tbody>

<tbody>
</tbody>