Given this table, chart and trend lines in Excel.
I want to find the coordinates of the intersecting point.
How can I do this?
3 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
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.
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.
