Plotting thru 2 fixed points

[celliott]

excel 2013: how can I fix or anchor both ends of a trendline, but allow the data in between to determine the shape of the line? Basically - fix the end points?

[MrShorty]

I don't think the chart trendline feature will do that. The way I would do it would be to calculate/enter the desired endpoints in the spreadsheet, then add that data series to the chart. If you need to find slope and intercept from the desired endpoints, you can either find them algebraically or use the LINEST() function to calculate the slope and intercept of the line connecting those two points.

[celliott]

Hmm.... I know I have a really weird case. And thanks. I was hoping there was a way with a series like this:
2012 890
2013 989
2014 878
2015 956
2016 1,100
2017 991
2018 890
2019 983
2020 1000

To have the trendline START at 890, and END at 1000 - and let the other numbers influence the shape.

Signed, a very visual person.

[MrShorty]

How do you want the other numbers to influence the shape? Are you looking for something that will smooth out the fluctuations or do want to keep the cyclical up and down?

You could just plot the data as is in a line or scatter plot formatted as a smoothed line connecting the points. The smoothed curve will connect each point with what is called a cubic spline. It will be very difficult to back out the equations that describe this curve -- if that is what you need to do.

If your algebra skills are up to the challenge, you could use a polynomial (or other suitable trendline equation). You would need to do some algebra on the polynomial to find expressions for two of the parameters based on the two endpoints, then use the LINEST() function to obtain the remaining parameters.

Solver might be easier, though it should amount to almost the same thing. Set up some of kind of regression spreadsheet, then use Solver to optimize your "goodness of fit" statistic based on the constraints that y(2012)=890 and y(2020)=1000.