New Contributor

# Excel intersect

At the moment I am making a return on investment for a product we sell. I made a graph and in that graph, the two lines are intersecting. I want to know the concrete number where the lines are intersecting but I don't know which formula to use. And I cannot use the normal formula because the numbers are constantly changing due to the yearly costs that are coming from the investment.

4 Replies

# Re: Excel intersect

Try this for the ROI data, the return the break-even years.

``=(2*B5-B6-2*C5+C6)/(C6-C5-B6+B5)``

You can create a similar formula for the other one. Example file attached.

# Re: Excel intersect

I understand the way you are working. But I checked your figures and formula but I cannot come to a point where the intersection in my graph is exactly the same as the number out of the formula. And I find it weird that your intersection is in a different year than mine.

# Re: Excel intersect

@Kay_v I overlooked that your series started counting at year zero, but it doesn't really matter. In your graph the intersect is between 7 and 8. Counting from 0, that compares to between 8 and 9 counting from 1. So, guess we need to reduce the calculation by 1 to reflect this.

``=(2*B5-B6-2*C5+C6)/(C6-C5-B6+B5)-1``

# Re: Excel intersect

That is true thanks for the help!