PDA

View Full Version : Caculate Distance between 2 points on x y scatter plot chart



b_rianv
10-03-2016, 06:15 PM
Good evening everyone,

I would like some help in trying to achieve this last project for my workbook. It is finding out the distance between (2) points on my (x, y) scatter plot chart. If you look at the chart in the (B_D sec 9) workbook you will see the chart as well as my "Distance Calculations from (Point TO Point) table. I have a few examples I typed in to this table for you too see what I want to do. These "Point "A" and Point "B" refer to the numbers on my cart and I want to calculate the distance between these too points in feet and also display what these 2 points "A" and "B" are in my "Category of Point "A" / "B".

The size of my (x, y) scatter plot in FEET are these dimensions
Y axis (North and South) = 1,290 ft..
X axis (East and West) = 1,790 ft..
If we start from the origin of x=0, y=0 then we would divide the overall distances in half
Y axis (North and South) = 645 ft. in one direction (+)
Y axis (North and South) = 645 ft. in the other direction (-)
Y axis total footage is = 1,290 ft.
If we start from the origin of x=0, y=0 then we would divide the overall distances in half
X axis (East and West) = 895 ft. in one direction (+)
X axis (East and West) = 895 ft. in the other direction (-)
X axis total footage is = 1,790 ft.

So how do I calculate the distance now between these 2 points? Does there have to be a "Z" axis incorporated for the calculations to happen?
How do I get the get category for the point "A" /"B" to show up in the Distance Calculations from Point to Point table?
Can this be done by Macros or a combination of both Macros and formulas?

All data for the chart comes from the data workbook, play with it and you will see how I have laid it out to work. Any thought and changes to this workbook are welcomed.

Also there is a PROBLEM with one of my macros I can't seam to find and it is this...
If you click on a point on my chart you will see that the workbook snaps down to the name and highlights it. Well for some reason and why I do not know, if you click one of the points that has a reference not only to my tables below the chart but also in the "Distance Calculations from Point to Point table "this word appears in both charts then the workbook will not snap down or highlight, it will just put a border around the word in the Distance Calculations from Point to Point table. Why I do not know.

Any help would be great in helping me achieve this last project. Please feel free to change, add, amend the workbook.

NOTE: I had to remove my satellite image from the workbook X Y scatter plot chart as it made my file to big to upload but everything still works, sorry!

Thank you for your time,
Brian

b_rianv
10-04-2016, 02:07 AM
So tis isn't possible, what I want to do?

Paul_Hossler
10-04-2016, 10:45 AM
I'd expect the distance between two points to be the Pythgarium theorem




Option Explicit

'X and Y in feet, Z in inches depth so make negative

Function DistXYZ(X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2 As Double) As Double
DistXYZ = Sqr((X2 - X1) ^ 2 + (Y2 - Y1) ^ 2 + (-Z2 / 12# + Z1 / 12#))
End Function

b_rianv
10-04-2016, 11:22 AM
Thank you Paul for this.