#ELBOW METHOD XLSTAT HOW TO#

Step Four вЂ“ Calculate the mean (average) of each cluster set Figure 6 Referring back to Figure 4, we then find the minimum distance for each case from each of the three start points вЂ“ this tells us which cluster (1, 2 or 3) that the case is closest to вЂ“ which is shown in the вЂinitial choice columnвЂ™. We can do it вЂњmechanicallyвЂќ as shown here вЂ“ but Excel has a built-in formula to use: SUMXMY2 вЂ“ this is far more efficient to use. We calculate the difference between each of the three data points in the set, and then square the differences, and then sum them. We want to calculate the distance and we use the sum of squares method вЂ“ as shown here. Remember that we have arbitrarily designated Case 6 to be our random start point for Cluster 1. LetвЂ™s look at the first number in the table вЂ“ case 1, start 1 = 10.54. You should note that the intersection of each of these gives a 0 (-) in the table.

Start 1 is the data for case 6, start 2 is case 9 and start 3 is case 15. Referring to the table output вЂ“ this is our first calculation in Excel and it generates our вЂњinitial choiceвЂќ of clusters. Please refer to the article on why cluster analysis sometimes generates different results. This suggests that these three cases are somewhat different to each other, so good starting points as they are spread out. The reason I selected these cases is because вЂ“ when looking at variable X only вЂ“ case 6 was the median, case 9 was the maximum and case 15 was the minimum. For these start points I have selected cases 6, 9 and 15 вЂ“ but any random points could also be suitable. In this example вЂ“ as IвЂ™m wanting to create three clusters, then I will need three starting points. Figure 4ДЇor k-means clustering you typically pick some random cases (starting points or seeds) to get the analysis started. Please note that you can use this Excel approach to identify as many clusters as you like вЂ“ just follow the same concept as explained below. Yes, there are four clusters evident in the diagram above, but that only looks at two of the variables. Step Three вЂ“ Calculate the distance from each data point to the center of a clusterДЇor this walk-through example, letвЂ™s assume that we want to identify three segments/clusters only.

#ELBOW METHOD XLSTAT HOW TO#

As I have suggested, a good approach when there are only two variables to consider вЂ“ but is this case we have three variables (and you could have more), so this visual approach will only work for basic data sets вЂ“ so now letвЂ™s look at how to do the Excel calculation for k-means clustering. With this next graph, I have visibly identified probable cluster and circled them. In this case, you could identify three or four relatively distinct clusters вЂ“ as shown in this next chart. And, at times, you can cluster the data via visual means.ДЄs you can see in this scatter graph, each individual case (what IвЂ™m calling a consumer for this example) has been mapped, along with the average (mean) for all cases (the red circle).Дepending upon how you view the data/graph вЂ“ there appears to be a number of clusters. In this cluster analysis example we are using three variables вЂ“ but if you have just two variables to cluster, then a scatter chart is an excellent way to start. Step Two вЂ“ If just two variables, use a scatter graph on Excel Figure 2

You can see from this example set that three start positions have been highlighted вЂ“ we will discuss those in Step Three below. ItвЂ™s just easier for me to classify that person in the вЂњover $250,000вЂќ income bracket and scale income 1-9 вЂ“ but thatвЂ™s up to you depending upon the data you are working with.

Say, for example, I am using income data (a demographic measure) вЂ“ most of the data might be around $40,000 to $100,000, but I have one person with an income of $5m. The reason for this is to вЂњcontainвЂќ any outliers. NOTE: I prefer to use scaled data вЂ“ but it is not mandatory.

Elbow method xlstat