0

I have a graph with one series of data, with a trend line and an associated trend line equation (y = mx + b). I have another series of data, but only of y-values. I want to find the x-values of this set of data by seeing where the y-value would intersect on the trend line. How do I do this in Excel?

  • 3
    Not sure if i am missing something here, but maybe just apply good old maths: if y =mx + b then x = (y - b)/m – TheUser1024 Jun 22 '14 at 21:03
-1

While I cannot tell you specifically how to ask Excel to do this, I can tell you how it is done in general.

The first thing you need is two points from each of the lines, so you will have four points total. With the x and y values for each of these points you will have 8 values total. If we call two points on the first line point 1 and point 2, and the two points on the second line point 3 and point 4, we end up with x1, y1, x2, y2, x3, y3, x4, y4. You can now find the point at which these two lines would potentially intersect. We'll call the x coordinate of the intersection point px and the y coordinate of the intersection point py. The following will provide them:

px = ((x1 * y2 - y1 * x2) * (x3 - x4) - (x1 - x2) * (x3 * y4 - y3 * x4)) / ((x1 - x2) * (y3 - y4) - (y1 - y2) * (x3 - x4));

py = ((x1 * y2 - y1 * x2) * (y3 - y4) - (y1 - y2) * (x3 * y4 - y3 * x4)) / ((x1 - x2) * (y3 - y4) - (y1 - y2) * (x3 - x4));

Now you just need to test to see whether this point of intersection actually falls on both of the lines in question. If the lines are both infinite, then you have found your answer. Otherwise something like the following will work:

//check x
    if (x3 < x4) {
        if (px > x3 && px < x4) {
            xInRange1 = true;
        }
    } else {
        if (px > x4 && px < x3) {
            xInRange1 = true;
        }
    }
    //check y
    if (y3 < y4) {
        if (py > y3 && py < y4) {
            yInRange1 = true;
        }
    } else {
        if (py > y4 && py < y3) {
            yInRange1 = true;
        }
    }

    //same for other line
    //check x
    if (x1 < x2) {
        if (px > x1 && px < x2) {
            xInRange2 = true;
        }
    } else {
        if (px > x2 && px < x1) {
            xInRange2 = true;
        }
    }
    //check y
    if (y1 < y2) {
        if (py > y1 && py < y2) {
            yInRange2 = true;
        }
    } else {
        if (py > y2 && py < y1) {
            yInRange2 = true;
        }
    }

Finally, if all four InRange values are true, then you actually have an intersection point. Otherwise there is an intersection point that is somewhere beyond the endpoints of the lines.

if (xInRange1 && yInRange2 && xInRange2 && yInRange2) --Hurray, intersection point!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.