Excel - 3D cartesian points - euclidean distance for a large group of points

Question

I have a large set of XYZ Cartesian points in Excel (some 40k actually) and was looking for a formula or macro to compare every point to every other point to get the distances between them.

The math to get the distance value between two 3D points is:

Distance=SQRT((X2 – X1)^2 + (Y2 – Y1)^2 + (Z2 – Z1)^2)
X1=the X value of the 1st point
X2=the X value of the 2nd point
Y1=the Y value of the 1st point
Y2=the Y value of the 2nd point
etc

Here is an example starting with 10 points:

http://i.imgur.com/U3lchMk.jpg

Would anyone know of a way to build this into Excel so that I can just copy the formula across the page to the horizontal limit? Or would you recommend a better way than using Excel?

As a secondary goal, I want to group the points into clusters that can connect by a distance lower than 2. But if I can accomplish the first goal, I can worry about the second later.

Actually, I was able to come up with the solution with a bit more research: i.imgur.com/9JL5Qni.jpg =SQRT(((INDIRECT("A"&$D2))-(INDIRECT("A"&E$1)))^2+((INDIRECT("B"&$D2))-(INDIRECT("B"&E$1)))^2+((INDIRECT("C"&$D2))-(INDIRECT("C"&E$1)))^2) — Cyclone

Cyclone Cyclone · Accepted Answer · 2016-03-01T15:12:34

Actually, I was able to come up with the solution with a bit more research: i.imgur.com/9JL5Qni.jpg =SQRT(((INDIRECT("A"&$D2))-(INDIRECT("A"&E$1)))^2+((INDIRECT("B"&$D2))-(INDIRECT‌("B"&E$1)))^2+((INDIRECT("C"&$D2))-(INDIRECT("C"&E$1)))^2)

Excel - 3D cartesian points - euclidean distance for a large group of points

1 Answers