Forum Discussion
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
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.
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.
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
