Solved

# clustering values by their distances from each other in excel

Posted on 2011-02-16
Medium Priority
295 Views
Good afternoon!

the problem I think is probably very simple, but I am stuck,
I have a list of coordinates with their ID numebrs and would like to cluster the points that are at a distance less than 20 to each other.
To start with, I sort the data by the X then the Y column so that the points that would cluster would already be grouped together. On a separate column, I calculate the distances. From these I would like to then group the values by a letter name. From that I can then set up a table with the number of encounters for each group, together with the average values for each group.
I have to do that for several hundreds of sheets, and get the table with the averages for each one.
I have enclosed a spreadsheet as it is easier to follow I think!
Many thanks for any help!
cluster1.xls
0
Question by:Daniele Questiaux

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 34914653
In cell I3 type the letter "A"

and in cell I4 paste the formula

=IF(SQRT(((G3-G4)^2)+((H3-H4)^2))<20,I3,CHAR(CODE(I3)+1))

and copy it down

Saqib
0

Author Closing Comment

ID: 34914946
Thank you! This is a neat and simple method of applying this formula to all the sheets.
I never thought of using the char functions, and it is a good way of starting.
Many many thanks
Danièle
0

LVL 45

Expert Comment

ID: 34914955
How do you decide what letter to assign to a group or individual result?
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.