Cluster analysis is a powerful technique that you
can use to get great insights into your data.
However, the workbook that you built in this course does have
some idiosyncrasies and I want to show you how to handle them.
My sample file is the trouble shooting workbook and you can
find it in the chapter three folder of your exercise files collection.
This workbook is a macro enabled workbook.
But you might find that due to your company's information security policy
or just Excel settings that you are unable to run the macros.
If that's the case I'll show you where to
look so that you can turn them on if possible.
To change Excel's macro security policy click the
File tab, and then click the Options item.
Doing so displays the Excel options dialog box.
Now, click trust center, over here in the categories pane.
And then click trust center settings button.
Doing so displays the trust center dialog box and click if necessary macro settings.
On this page you can select from four different macro settings.
The one you should never select is enable all macros.
That allows datas code to run and it's a bit idea.
Next you might see disable all macros except digitally signed macro's.
If that option is selected or if
disable all macros without notification is selected.
Then, if possible and you want to run
macros, you should select Disable All Macros with Notification.
What that setting does is it displays a
message bar indicating that the workbook contains macros, and
it disables them, but allows you to click a
button to enable the content to turn them on.
Because I'd worked with this file inside of Excel previously
and had enabled its macros, Excel didn't ask me again.
So, the option you want to select is disable all macros with notification.
If you're not sure what to do, always check
with your company's IT department to see what's allowed.
With that option selected, go ahead and click okay, and click okay again.
And the other problem you can run into is
when one of the centroids has no nearest points.
So let me just generate some random centroids.
And eventually in the Centroid Updates Table, I should see divide by zero.
And there it is.
So, I have a divide by zero error.
So, if I were to update the table with a divide by
zero, then some of the centroids here would have errors in the calculations.
As soon as you see divide by zero in
the centroids area, generate a new set of random centroids.
So I'll go ahead and do that.
I got another divide by zero.
And these values are fine.
So when I click update,
everything looks good.
So remember, if you see a divide by zero error, immediately generate new Centroids.
Finally, it is possible that you won't get
meaningful solutions when you run your centroid analysis.
As I developed this course, I discovered several starting
positions that lead to results that don't make any sense.
So, instead of generating random centroids, I will
enter in the values that I know to bed.
So in cell I3, I'll type 21.
Then the tab, and then day 12, tab
twice the second centroid, is 23.5
and then 13.25, tab twice and the
final one is 7.5 and 5.2 and enter.
So there I have my Centroids.
And you can see that there's one here between these
two groups, and the other two, which are up here.
And this little cluster at the top.
And if I click Update Centroids, you see that I don't get any movement.
Instead of having the centroids move to the middle of
each of what I consider to be three pretty obvious clusters.
I get the one here for these points and I
get the two centroids here for a much smaller group.
So this result doesn't make sense.
It's reasonably easy to see this result doesn't make
any sense in two dimensions because the chart works.
However, if you're working with data tables that have more dimensions say
four, five, six, possibly even as many as 10 or 12 columns,.
Then you won't be able to see the results, because you can't create a chart.
So the best thing to do, is, to run
many, many, many different trials, of your cluster analysis.
Then analyze the data in your Excel table, and see if the results make sense.
So now I hope that you know what to look for, have an
understanding of what might go wrong, and also how to deal with it.