Store Clustering on an excel spreadsheet

[Peter Davison]

Hi,

I've been doing a bit of store clustering on a spreadsheet (using a sort of K Means type formula and whilst I can cluster a single attribute I'm not sure how I can establish what the store cluster would be for a group of attributes.

For Example

Min	2.46%	3.45%	1.29%	22.42%	8.40%	20.25%
Max	23.67%	11.48%	4.65%	47.05%	18.19%	40.94%
Grand Total	12.68%	6.09%	3.16%	34.74%	14.33%	29.00%
	FRUIT	FTG SALADS	SALAD ADDITIONS	SALADS	STIR FRY	VEG

For each attribute individually - Fruit, FTG Salads etc I have established the Min and Max % contribution and what the whole stores average % is. (Above)
and then clustered using 5 clusters (could be any number) and just 10 iterations which then places each store in one of the 5 clusters During the 10 iterations some stores move cluster to be closer to each new centre of the cluster.

This is an example of a few stores below

Store	FRUIT	FTG SALADS	SALAD ADDITIONS	SALADS	STIR FRY	VEG
101​	13.04%	5.83%	3.76%	32.68%	15.16%	29.54%
102​	16.17%	7.21%	3.24%	29.47%	13.43%	30.49%
104​	9.97%	5.44%	2.04%	41.25%	14.96%	26.35%
105​	10.92%	4.11%	2.19%	42.08%	14.50%	26.19%
108​	12.66%	6.64%	2.97%	36.19%	14.62%	26.92%
109​	9.42%	7.19%	2.44%	27.28%	15.54%	38.13%

Each % by store will add up to 100% for all attributes.

What I can't work out is for each attribute Fruit, FTG Salads etc the store may be in a different cluster, so what would I do if I wanted to find what cluster a store should be in taking into consideration all 6 attributes - Fruit, FTG Salads, Salad Additions, Salads, Stir Fry and Veg?

I always set the cluster starting point lowest% to highest%

At the moment I have a sheet for each attribute.

If anyone has any ideas how I could do this using a spreadsheet I would be really pleased.

 

[Rijnsent]

Hi Peter,
for statistical questions in Excel I generally refer people to Performing Real Statistical Analysis Using Excel , for cluster analysis: Real Statistics k-means | Real Statistics Using Excel , that has a pretty decent manual. It requires their free toolpack with extra formulas but seems to do the trick. Note that in cluster analysis you'll always have outliers that don't seem to fit, but the analysis does help to cluster the ones that are closest/most comparable to eachother.
Cheers,
Koen

 

[Peter Davison]

Thank you for your help and assistance