Distance Calculations - Is there an easier way.

[Guest]

I currently am using a workbook with 1 sheet of coordinates (X,Y). I created
the following formula a few years back, is there any way to trim it, as it
makes my workbook in excess of 32MB. I am currently using approx 1,000
points. On the sheet with the distances on it this is the formula that I use
with header rows and columns.

=SQRT((SUMSQ(ABS((VLOOKUP(A2,$A$510:$C$1012,2))-(VLOOKUP(B1,$A$510:$C$1012,2)))))+(SUMSQ(ABS((VLOOKUP(A2,$A$510:$C$1012,3))-(VLOOKUP(B1,$A$510:$C$1012,3))))))

This was a difficult formula to make work, and I am sure there must be an
easier way. I am just trying to create a calculation chart for the distance
between the two points. My basic spreadsheet that identifies this is set up
similar to a multiplication table.

Any help is appreciated.

 

[Harlan Grove]

I currently am using a workbook with 1 sheet of coordinates (X,Y). I
created the following formula a few years back, is there any way to
trim it, as it makes my workbook in excess of 32MB. I am currently
using approx 1,000 points. On the sheet with the distances on it this
is the formula that I use with header rows and columns.

....

First, drop the unnecessary parentheses. Then drop the unnecessary ABS calls
since SUMSQ(ABS({a,b}-{c,d})) = ABS(a-c)^2 + ABS(b-d)^2 = (a-c)^2 + (b-d)^2
= SUMSQ({a,b}-{c,d}). Then eliminate the second SUMSQ call and pass the
first call the difference between two arrays.

=SQRT(SUMSQ(VLOOKUP(A2,$A$510:$C$1012,{2,3})
-VLOOKUP(B1,$A$510:$C$1012,{2,3})))