Excel formula for Euclidean distance

Question

A former co-worker of mine uses this formula to do some cluster analysis:

{=SQRT(SUM(($C3:$F3-$C$11:$F$11)^2))} . 

He doesn't know why it works.

It's meant to find the distance between some points. When I run the equation without the {} it gives me one answer. When I run it with it, I get another.

Distance is normally A^2+B^2=C^2. I'm guessing this is doing it for a series of points in the row. Is this basically doing the each point in each of these rows?

I just want to make sure we're not using something stupid.

Answer 1

It is an array formula that takes the squared differences between the corresponding cells, sums those values and takes the square root of the sum. So yes, it is a valid Euclidean distance in R4. Array formulas require hitting CTRL + SHIFT + ENTER at the same time. Otherwise it will return a value for the corresponding row/column.

That formula is equivalent to:

=SQRT(SUM((C3-C11)^2, (D3-D11)^2, (E3-E11)^2, (F3-F11)^2)

And there is a built-in function SUMXMY2 which does the same job:

=SQRT(SUMXMY2(C3:F3,C11:F11))

Answer 2

Euclidean Norm of a vector of size 'n' = SQRT(SUMSQ(A1:An))

The SUMSQ function is useful to calculate the Euclidean norm in Excel