Join Curt Frye for an in-depth discussion in this video Identifying each row's closest centroid, part of Up and Running with Excel Cluster Analysis.
- View Offline
…After you calculate the distance from a…point to each of the centroids you've created,…you need to define a formula that…determines which centroid is closest to each point.…In this movie I will show you how to create…those formulas using a combination of the If and Min functions.…I will use nested If formulas.…That way I can check for multiple conditions.…I'll start by clicking in the cell F3, and type in equal sign, and then If.…The If function checks to see if a condition is true.…
If it is true such, as a value greater than five, then it does one thing.…On the other hand, if the condition isn't true, then…it does something else, whatever the false condition action is.…So I'll start by typing a formula that will be intentionally incomplete.…We'll work with it in a moment.…So I have If, then a left parenthesis,…C3, which is the distance to the first centroid.…If C3 equals the minimum, M-I-N, left…parentheses, of the values in the cell range C3 to E3.…
So what I've done is typed in so far, If, left parentheses, C3 equals Min,…
- What is clustering?
- Setting up your Excel table
- Creating a list of centroids
- Identifying the closest centroid
- Updating centroids using averages
- Automating updates with macros
- Analyzing your data in Excel
- Visualizing data in a chart