Finding Intersection between 2 Trend Lines

Short but useless answer -- yes this is achievable.

Can you clarify some things -- the blue line looks like it is simply interpolating between points of that series, is that correct? Will the red line always be a vertical line?

If I have interpreted correctly, and I assume that the red line will always be vertical and the intersection point will always be between the 5th and 6th data point, then I would use a simple TREND() function: =TREND($E$7:$E$8,$D$7:$D$8,$B$22)

If I cannot be sure that the intersection point will always be between the 5th and 6th point, but could be between any two points, (still assuming red line is always vertical), then I would use a linear interpolation algorithm (mostly a lookup problem) to find the segment that contains the intersection point, then find the y value and red's x value from the slope and intercept for that point. A couple of examples of how I might implement an interpolation algorithm:
https://www.excelforum.com/excel-for...ml#post4873049
https://www.excelforum.com/excel-cha...ml#post3904113

If I cannot assume that the red line will be vertical, then I would perform an interpolation based on both curves, then use solver/goal seek to find the point where interpolation_blue-interpolation_red=0. I'm assuming that there will only be one intersection point for red and blue. If/when it is possible for there to be multiple intersection points, I have to be more careful to be sure it converges on the desired intersection.

Does that help? What questions do you have in implementing any of these? Have I misunderstood anything?

I wouldn't call this trend lines :-) Anyway, you may start with finding which point has first larger or equal X:

Formula:

=MATCH(B23,D3:D11,-1)

Its value (in this case 69.64) will be easy to find with:

Formula:

=INDEX(D3:D11,MATCH(B23,D3:D11,-1))

and smaller than (12.87) by:

Formula:

=INDEX(D3:D11,MATCH(B23,D3:D11,-1)+1)

similar formulas for their respoecive Y values

Formula:

=INDEX(E3:E11,MATCH(B23,D3:D11,-1))

=INDEX(E3:E11,MATCH(B23,D3:D11,-1)+1)

then Y for given X can be found from simple proportion
Y = y_too_small + (x_too_big - X)/(x_too_big - x_too_small)*(y_too_big - y_too_small)

so in excel:

Formula:

=INDEX(E3:E11,MATCH(B23,D3:D11,-1))+(INDEX(D3:D11,MATCH(B23,D3:D11,-1))-B23)/(INDEX(D3:D11,MATCH(B23,D3:D11,-1))-INDEX(D3:D11,1+MATCH(B23,D3:D11,-1)))*(INDEX(E3:E11,1+MATCH(B23,D3:D11,-1))-INDEX(E3:E11,MATCH(B23,D3:D11,-1)))

BTW, I hope I've not mismatched any ranges - It's much easier to write such formulas with sample workbook to test them :-) But you attached none

Either kaper's or my solution should work for this. Did you have a chance to try either or both of them? Do you have specific questions about implementing one of them?

Assuming your data is always monotone non-increasing as in your example,

	A	B	C
2	X	Y
3	4.00	211
4	4.67	211
5	10.66	199
6	12.87	187
7	69.64	112
8	70.11	112
9	72.32	100
10	72.32	0
11	75.00	0
12
13	X	Y
14	49	139.27	B14: =PERCENTILE(B3:B11, 1 - PERCENTRANK(A3:A11, A14, 8))

Your two different Y values for X = 72.32 are a little sketchy.