Excel X/Y Scatter Chart - Intersection of two lines changes as you rescale the axes

[mike geologist]

I am using Excel to plot directional data from a horizontal well to determine where the wellbore crosses specific geographic boundaries. In its simplest form, I am graphing two line segments and illustrating where they intersect.

I can calculate the exact intersection by defining the equation for each of these lines and solving them simultaneously, but I need the "map" to provide visual impact for my "audience".

Unfortunately, I noticed that the apparent intersection does not remain consistent as I "zoom" in by changing the X/Y scales on my charts. The more I "rescale in", the farther from the calculated intersection the lines appear to cross.

For example, consider the following simplified case:

Line Segment 1 (geographic boundary)
X1 = 1671406.83 Y1 = 451812.40
X2 = 1676024.51 Y2 = 451691.16

Line Segment 2 (wellbore)
X1 = 1674967.75 Y1 = 451762.98
X2 = 1674963.32 Y2 = 451669.09

If I graph this data using an X/Y Scatter plot I get the following apparent intersections:

Graph Scale "10" X Axis: 1674950 to 1674500 Y Axis: 451700 to 451740 Apparent Intersection: 451718.5

Graph Scale "5" X Axis: 1674955 to 1674980 Y Axis: 451710 to 451730 Apparent Intersection: 451719.0

Graph Scale "2" X Axis: 1674962 to 1674972 Y Axis: 451721 to 451729 Apparent Intersection: 451725.0

Graph Scale "1" X Axis: 1674965 to 1674969 Y Axis: 451749 to 451753 Apparent Intersection: 451750.7

The rigid matrix math calculation for this intersection is 451718.964

As I examine these graphs in detail, the position of the two line segments moves relative to the major grid lines and that is why the apparent intersection is different on each graph.

Has anyone else experienced this issue?
Is this a function of the way Excel constructs the graphs?
Is there a fix that stabilizes where the line segments appear on the graph?

Background Information:

• Excel 2013 version 15.0.4823.1004

 

[AlphaFrog]

I can duplicate your problem in Excel 2003. I can't explain it though. It appears to change the visual intersect only when one or more of the end points is scaled off the plot area.

A band-aid solution is to calculate the intersect coordinates (as you have done) and add them as a data point for each data series.