Excel intersect

%3CLINGO-SUB%20id%3D%22lingo-sub-3481737%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20intersect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3481737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1417532%22%20target%3D%22_blank%22%3E%40Kay_v%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20for%20the%20ROI%20data%2C%20the%20return%20the%20break-even%20years.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D(2*B5-B6-2*C5%2BC6)%2F(C6-C5-B6%2BB5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BYou%20can%20create%20a%20similar%20formula%20for%20the%20other%20one.%20Example%20file%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3481801%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20intersect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3481801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20the%20way%20you%20are%20working.%20But%20I%20checked%20your%20figures%20and%20formula%20but%20I%20cannot%20come%20to%20a%20point%20where%20the%20intersection%20in%20my%20graph%20is%20exactly%20the%20same%20as%20the%20number%20out%20of%20the%20formula.%20And%20I%20find%20it%20weird%20that%20your%20intersection%20is%20in%20a%20different%20year%20than%20mine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3481944%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20intersect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3481944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1417532%22%20target%3D%22_blank%22%3E%40Kay_v%3C%2FA%3E%26nbsp%3BI%20overlooked%20that%20your%20series%20started%20counting%20at%20year%20zero%2C%20but%20it%20doesn't%20really%20matter.%20In%20your%20graph%20the%20intersect%20is%20between%207%20and%208.%20Counting%20from%200%2C%20that%20compares%20to%20between%208%20and%209%20counting%20from%201.%20So%2C%20guess%20we%20need%20to%20reduce%20the%20calculation%20by%201%20to%20reflect%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D(2*B5-B6-2*C5%2BC6)%2F(C6-C5-B6%2BB5)-1%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3482001%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20intersect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3482001%22%20slang%3D%22en-US%22%3EThat%20is%20true%20thanks%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3481504%22%20slang%3D%22en-US%22%3EExcel%20intersect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3481504%22%20slang%3D%22en-US%22%3E%3CP%3EAt%20the%20moment%20I%20am%20making%20a%20return%20on%20investment%20for%20a%20product%20we%20sell.%20I%20made%20a%20graph%20and%20in%20that%20graph%2C%20the%20two%20lines%20are%20intersecting.%20I%20want%20to%20know%20the%20concrete%20number%20where%20the%20lines%20are%20intersecting%20but%20I%20don't%20know%20which%20formula%20to%20use.%20And%20I%20cannot%20use%20the%20normal%20formula%20because%20the%20numbers%20are%20constantly%20changing%20due%20to%20the%20yearly%20costs%20that%20are%20coming%20from%20the%20investment.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3481504%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

@Kay_v 

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.

@Riny_van_Eekelen 

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

 

 

That is true thanks for the help!