Finding Intersections in Charts?

**Scott S.** · 12-17-2017, 02:15 PM

I have two points, each with a 95% confidence ellipse. I need to determine whether these points are identical or different based on a series of formulae. The formulae require critical overlap:seperation data.
So I need the distance from the center of each ellipse to its ellipse and the distance of overlap or separation between ellipses.
Can excel provide the x,y coordinates of the intersection of the line and ellipses?

I attached a pic of my charts, the yellow circles are the coordinates I need.
And I attached my work sheet.

Much appreciate ANY help.
Thanks, Scott

**MrShorty** · 12-17-2017, 07:29 PM

Here's how I would probably do this, especially if this is a one time or rare to occasional task. Note that I am using a numerical root finding algorithm (Solver or goal seek, Personally, I prefer Solver):

1) First, I note that you have not yet calculated the equation for the line. Since I need to be able to calculate y at any x for that line in order to find the intersection points the first step is to find slope and intercept for that line (or any other workable y=f(x) equation for that line). For now I will assume that you know how to get an equation for the line from the two points (if not, this algebra tutorial should refresh your memory: http://www.purplemath.com/modules/strtlneq.htm )
2) Make 2 copies of row 32 in two convenient rows somewhere below the main table.
3) In column K (or other suitable column) adjacent to one of these rows, enter your formula for y(line) based on the x value of one of the ellipses (column D for one, then column I for the other).
4) Add another adjacent cell with a simple subtraction formula: K-E for the blue ellipse and K-J for the red ellipse.
5) Because the intersection point is where the Y values are equal (their differences are 0), call Solver/Goal seek and tell it to:
5a) Set column L of one row (or whichever column you used for the difference formula)
5b) to a value of 0
5c) by changing the angle in column A of that row.
5d) Because there are obviously two possible intersection points for each ellipse, you may need to pay attention to the "initial" guess you enter for angle in column A before calling Solver/Goal Seek. If your initial guess causes the algorithm to converge on the intersection point on the "wrong" side of the ellipse, simply enter a better initial guess for the angle, and rerun Solver goal seek.
6) Repeat for the other row.

That should work well if this is an occasional or one time task. I think if I had to do this frequently or had several problems I needed to solve, I would spend some time with the algebra and see if I could find a closed form algebraic solution. This tutorial should help in this http://www.purplemath.com/modules/syseqgen.htm Note that page 6 includes an example of solving straight line + ellipse systems of equations using the quadratic formula and a fair amount of algebraic manipulation.