Clustering/Classification using Excel

[vioravis]

I am trying to do clustering/classification using the shortest euclidean distance. In the attached Excel spreadsheet, I am trying to classify new visits in Table 2 into one of the three visits given in Table 1. I have calculated the euclidean distance in Table 3 and classified them into one of the three visits.

The issue I have is that the number of columns in the original data is more than 100 and I don't want to do the euclidean distance for each column (square the difference for each columns and add them) separately. Is there any efficient way to do this classification? Thank you.

[DonkeyOte]

I'm no mathematician so I'll accept you know what you want to do all I can do is tell you the way to calculate it using formulae...

Please Login or Register  to view this content.

The above can be copied across you entire results matrix.

[vioravis]

Thanks. This worked fine. Is there any way to obtain the classification given in Column E of Clustering Table based on the minumum values on each row?

[DonkeyOte]

Yes

Please Login or Register  to view this content.

[vioravis]

Thanks, this worked fine again.

In the first formula you gave for calculating the euclidean distances, can you please explain the logic behind choosing zero for col_num in the INDEX function? Thank you.

[shg]

The zero is an argument to MATCH function, not INDEX. It specifies an exact match.

[martindwilson]

this bit
=SUMPRODUCT((($B10:$E10)-(INDEX($B$3:$E$5,MATCH(B$17,$A$3:$A$5,0),0)
i was puzzling on that too

[vioravis]

shg,

I am still puzzled. I think the zero you are referring to is inside the MATCH bracket. I am referring to the one outside. Am I missing something here? Thanks.

[DonkeyOte]

B18:
=SUMPRODUCT((($B10:$E10)-(INDEX($B$3:$E$5,MATCH(B$17,$A$3:$A$5,0),0)))^2)

Both MATCH & INDEX are using 0

This...

Please Login or Register  to view this content.

Will return the Row index position in which B17 appears within A3:A5... this value is used in the index to establish our range of values that are to be subtracted from B10:E10...

Please Login or Register  to view this content.

The use of 0 in the INDEX will ensure an array of values is returned - ie all columns are returned in the referenced range... so in essence the above will reference B3:E5 and return contents of B:E for the row as determined by the MATCH function.

The Sumproduct then "iterates" through B10:E10 and for each subtracts the appropriate value from Bx:Ex (where x is determined by the MATCH) and squares the difference... the result = sum of all these "iterations".

[vioravis]

Thanks. This really helped.