Determine the Intersection

[xuetengwang]

Hi I have 2 sets of data. One straight line and one curve.

The curve consists of numbers that given, no formula...

Can anyone please tell me how to get the intersection of these 2 graphs?

I been struggling with it for hours, and just could not find a way. Any hints will be greatly appreciated. I posted the original attachment from my PC, worksheet: Intersection.

Thank you!![/CENTER]

[LJMetzger]

Here's an alleged algorithm off the top of my head:
a. Start at minimum x.
b. Compare the y values at the x point, and note which y is greater.
c. Repeat step b. until the other y is greater.
d. Now you have two sets of xy cooordinates for line 1 and
two sets of coordinates for line 2.
e. Assuming each is a straight line, calculate the equation for each line:
y1 = m1 * x1 + b1
y2 = m2 * x2 + b2
f. Where they intersect, both y1 and y2 will be the same, and x1 and x2 will be the same.
Everything else is known except for x and y yields: m1 * x + b1 = m2 * x + b2
g. Subtract (m2 *x ) from both sides yields: m1 * x + b1 - m2 * x = b2
h. Rearranging yields: x * (m1 - m2) + b1 = b2
i. Subtract b1 from both sides x = (b2 - b1)/(m1 - m2)
j. Now solve for y yields: y = m1 * x + b1
k. Double check : y = m2 *x + b2 should yield the same y.

Lewis

[xuetengwang]

Hi Lewis,

Thanks, but one set of data has no formula...

[MrShorty]

In which case, part of solving a problem like this is figuring out what formula to give that set of data (or at least, what formula to give it over the interval where it intersects the other line).

What Lewis has suggested is a basic linear interpolation algorithm. Locate the interval that contains the intersection, use the data points that define the ends of that interval to define a line segment that approximates each curve, then solve that system of equations. Since you seem a little rusty on your algebra, I would suggest you start by reviewing some basic algebra:

Finding the formula for a line connecting two points: http://www.mathsisfun.com/algebra/li...n-2points.html
Solving systems of equations: http://www.purplemath.com/modules/systlin1.htm

With these concepts, you should be able to use Lewis's overall idea to find the equations for the straight lines that would approximate where the two curves intersect, then use those two equations to find an intersection.

[FDibbins]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...ersection.html

[xuetengwang]

Hi Ford,

Are you saying that these websites are the same, as I am not aware of that...

I actually bookmarked both websites.

[FDibbins]

No they are not, we are not related to them in any way. It is a forum rule though (and also just plain common courtesy) to let people that are try to help you, know that you have also asked for help elsewhere. How would you feel if you put effort into helping with something, only to find out, the person already solved it somewhere else?

[bendb]

Hi,

Use interpolation with:
http://www.blueleafsoftware.com/Prod...ationExcel.php
or
http://xongrid.sourceforge.net/

For each of your two functions create interpolation f1(x) and f2(x) calculate the difference and search the zero of the difference by changing x with the excel solver