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:
[TABLE="width: 132"]
<tbody>[TR]
[TD="align: right"]3/20/2017[/TD]
[TD="align: right"]62.7%[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2017[/TD]
[TD="align: right"]58.5%[/TD]
[/TR]
[TR]
[TD="align: right"]4/17/2017[/TD]
[TD="align: right"]62.2%[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]60.5%[/TD]
[/TR]
[TR]
[TD="align: right"]5/8/2017[/TD]
[TD="align: right"]60.9%[/TD]
[/TR]
[TR]
[TD="align: right"]5/15/2017[/TD]
[TD="align: right"]55.6%[/TD]
[/TR]
[TR]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]58.0%[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

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
intercept
slope

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Date (formatted)[/TD]
[TD="bgcolor: #FFF2CC"]Value (formatted)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3/20/2017[/TD]
[TD="align: right"]62.70%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4/10/2017[/TD]
[TD="align: right"]58.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4/17/2017[/TD]
[TD="align: right"]62.20%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]60.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/8/2017[/TD]
[TD="align: right"]60.90%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5/15/2017[/TD]
[TD="align: right"]55.60%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]58.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FCE4D6"]Date (unformatted)[/TD]
[TD="bgcolor: #FCE4D6"]Value (unformatted)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]42814[/TD]
[TD="align: right"]0.627[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30.3539[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]42835[/TD]
[TD="align: right"]0.585[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-0.00069[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]42842[/TD]
[TD="align: right"]0.622[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]42856[/TD]
[TD="align: right"]0.605[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]42863[/TD]
[TD="align: right"]0.609[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]42870[/TD]
[TD="align: right"]0.556[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]42884[/TD]
[TD="align: right"]0.580[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet49​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]A12[/TH]
[TD="align: left"]=A2[/TD]
[/TR]
[TR]
[TH="width: 10"]B12[/TH]
[TD="align: left"]=B2[/TD]
[/TR]
[TR]
[TH="width: 10"]E12[/TH]
[TD="align: left"]=INTERCEPT(B12:B18,A12:A18)[/TD]
[/TR]
[TR]
[TH="width: 10"]E13[/TH]
[TD="align: left"]=SLOPE(B12:B18,A12:A18)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]