Get coordinates of intersecting point of two trend lines

Given this table, chart and trend lines in Excel.
I want to find the coordinates of the intersecting point.

How can I do this?

Anil

Posted 2016-08-10T10:30:20.503

Reputation: 207

Answers

Small solution for dummies like me

Right click each trendline » Format trendline » Display equation on chart
Open wolframalpha's sub site for Intersection points of two curves/lines
Back in Excel copy both formulas by double clicking them and paste them over to wolframalpha. (Note to me: Replace commas through dots)

You get the x-coordinate

and calculate the y-coordinate yourself by taking one of your two trendline formulas and insert your just calculated x value

y = 1.64 x 0.52245 + 0.034 = 0.890818

nixda

Posted 2016-08-10T10:30:20.503

Reputation: 23 233

Thanks for sharing the answer. I have small query. Steps 2 and 3 are OK. I got the equations y = 1.64x + 0.034 with R² = 0.9957 and y = -1.4686x + 1.6581 with R² = 0.9518 when I had considered these 2 sets of values separately. But, in the image that you had presented, its on the single set of data. Therefore the query is how to consider partial set of data for generating a trendline. – Anil – 2016-08-10T14:57:04.527

@Anil I'm sorry but I don't understand your last question Therefore the query is how to consider partial set of data for generating a trendline. I thought you already have 2 separate trend lines? Regarding my first picture: I just added 2 more value series to get 2 trend lines following this tutorial. First one for A2:B6 and second one for A7:B11. Your question was not about how to add trend lines. I thought you just want to know how to get the intersect point? Not?

– nixda – 2016-08-10T15:21:36.763

Yes My initial question was not on trendline. It was more on intersection. Later, i realised that i was not able to draw the 2 separate trendlines. Hence the addendum. Now, every thing is resolved.Thank you very much. your help is appreciated. – Anil – 2016-08-10T16:16:48.177

You can either configure the chart such that the linear expressions for the trend lines are shown or use the LINEST function to calculate constant part and slope for both lines.

To get the intersection point, you have to solve the resulting system of two linear equations as explained here.

Axel Kemper

Posted 2016-08-10T10:30:20.503

Reputation: 2 892

Click on the trend line, right click, then choose format trend line. Now choose the option "Display equation in chart". Once you have both equations displayed, equate them( you will have to write this out if you don't use some other tool) and obtain your point of intersection.

EDIT: check out nixda's answer, which illustrates my solution and provides an example of a tool you can use instead of writing the equations out.

MrFregg

Posted 2016-08-10T10:30:20.503

Reputation: 328