Is there a function or some other way to determine Euclidian distance using
Excel? Thanks. _c

Christina wrote:
Is there a function or some other way to determine Euclidian distance using
Excel? Thanks. _c


You just plug the normal equations into Excel. For 2 dimensional space
for example:

d = sqrt((x2-x1)^2 + (y2-y1)^2)

Bill

"Bill Martin -- (Remove NOSPAM from addre" wrote:

Christina wrote:
Is there a function or some other way to determine Euclidian distance using
Excel? Thanks. _c


You just plug the normal equations into Excel. For 2 dimensional space
for example:

d = sqrt((x2-x1)^2 + (y2-y1)^2)

Bill


Sorry, should have been much more specific about a few things:

1. The data is set up as follows: 50000 rows, column A contains a "work
group" designation (we can just use A, B, C, etc.) and column B contains an
age. Sample:

Work group Age
A 54
A 36
A 40
B 20
C 39
C 50

2. The specific formula we are looking for is the sqaure root of 1/n sum of
(x i - x j) ^2 where n = size of group and we want the sum of the differences
between each pair of observations in the group. So for the A's 54-36 and
54-30 as well as 36-40...does that make sense? The size of n varies.

3. I'm helping a friend out, so I may have her start posting as these types
of stats are not my area of expertise and I might be explaining things
poorly! (Apologies if that's the case!)

THANKS! _c

I see no *simple* way to do what you want in Excel. If I were doing
this I would pursue one of two approaches:

1) Write a function to do this using VBA. It looks like a fairly
straight forward two level nested loop in Basic. You'll need to have
some general programming experience though and some knowledge of VBA.

2) Buy a standard statistical package for Excel. Do a quick Google
search on the web for:

"Euclidian Distance" Excel

You'll find a bunch of people selling the software. You might even find
a free version somewhere from one of the universities if you spend
enough time trolling.

Now that I've said that, maybe someone here will pop up with a
blindingly simple way of doing this that I've overlooked.

Good luck...

Bill

"Bill Martin -- (Remove NOSPAM from addre" wrote:

I see no *simple* way to do what you want in Excel. If I were doing
this I would pursue one of two approaches:

1) Write a function to do this using VBA. It looks like a fairly
straight forward two level nested loop in Basic. You'll need to have
some general programming experience though and some knowledge of VBA.

2) Buy a standard statistical package for Excel. Do a quick Google
search on the web for:

"Euclidian Distance" Excel

You'll find a bunch of people selling the software. You might even find
a free version somewhere from one of the universities if you spend
enough time trolling.

Now that I've said that, maybe someone here will pop up with a
blindingly simple way of doing this that I've overlooked.

Good luck...

Bill


Thanks a lot for your effort. I don't feel so bad now, since we came to the
same conclusion. There were lots of very manual things we could think of,
but nothing *simple*. But I just wanted that "double check" from the
experts!

Christina wrote:

"Bill Martin -- (Remove NOSPAM from addre" wrote:

I see no *simple* way to do what you want in Excel. If I were doing
this I would pursue one of two approaches:

1) Write a function to do this using VBA. It looks like a fairly
straight forward two level nested loop in Basic. You'll need to have
some general programming experience though and some knowledge of VBA.

2) Buy a standard statistical package for Excel. Do a quick Google
search on the web for:

"Euclidian Distance" Excel

You'll find a bunch of people selling the software. You might even find
a free version somewhere from one of the universities if you spend
enough time trolling.

Now that I've said that, maybe someone here will pop up with a
blindingly simple way of doing this that I've overlooked.

Good luck...

Bill


Thanks a lot for your effort. I don't feel so bad now, since we came to the
same conclusion. There were lots of very manual things we could think of,
but nothing *simple*. But I just wanted that "double check" from the
experts!

Christina

The formula you give in your original post is the same as the
"population standard deviation".

your formula = STDEVP

It is not quite clear if you want to count in your formula both

(xi - xj)^2 and (xj - xi)^2

If yes, then

your formula = Sqrt(2) * STDEVP

You also may want to check out DSTDEVP because of the grouping.
You probably still will have to some code to loop around the groups.

HTH

Matthias Kläy
--
www.kcc.ch