Calculating the Intersection of 2 lines

G'day again forum goers, once again i find myself perplexed by something that should be relatively simple, namely;

I need to find a way of inputting the independent and dependent values that constitute a pair of lines (see attached excel document) and calculate the coordinates at the intersection of these lines automatically (in cells; D8 and D9). Since at the point of intersection, the two line equations will have the same values of x and y, the two equations equal to each other. This gives an equation with one unknown (x) which can be solved manually by rearranging to isolate the x terms however I need some kind of conditional equation that can perform the intersection calculation for a line with any slope and direction automatically (as it will need to be repeated hundreds of times).

For the example attached the value should equate to -4 and -2

Anyone have any ideas?

Regards
MattRNR

Here, I made recalculation.

And in A column I made two intercepts:
X->Y and Y->X

For both lines.

Then I use average to get interception of them.
I think it's fine but please test it.

Hi

Try attached, it uses MINVERSE and MMULT functions.

Let's know how you get on.

Alan

Cheers for the prompt responses guys, would you be able to clarify the MINVERSE and MMULT approach Alan? I've never come across these before and i can't seem to replicate your method (see attached), I can't really wrap my head around what it is doing, or how to get it to work.

Regards MattRNR

MattRNR

Hope this clears things for you.

There's lots of online help for MINVERSE, MMULT and other array functions. Help on matrices is another matter, if you're not familiar with them you may not want to use this solution. If you do, at least satisfy yourself that solutions are correct over a good sample range.

Let's know how you get on.

AlanRefined example of intersection ID(2).xlsx

Cheers for the help Alan
I’ve been reading up on the tools you suggested, admittedly I’m still having a bit of trouble wrapping my head around it. Would you be able to explain to me the steps you used to get the results you have? I hate to keep bothering you with this but I really need to figure this out to finish an analysis for my thesis and so far yours is the best solution I’ve been able to find.
Thanks again mate
My apologies for the late response.

Hi there

I've attached another example sheet and a Word 2010 document which I hope helps.

The Word doc attempts to explain what's going on. In the spreadsheet, the Basic tab allows you enter all your slope and intercept values. You can then choose which two to calculate. It automatically graphs the result.

The With Checks tab builds in some checks and allows to you to enter limits on any of the x or y values. If not applicable then leave the minimum and maximum values clear. Checks include lines aren't parallel, if you need limits, then it also checks mimumum limit is less than maximum, that they intersect etc. Perhaps you don't need them but I wanted to look at the problem because it appealed to me.

Good luck
Alan