 In the second video you saw how to use the Pythagorean theory to find the distance as the crow flies between two points. Then I showed you how to convert that into Excel by inserting two columns, finding the differences in locations of the first customer atoms in cluster one for each of the 32 offers. We then squared the difference and summed them. We found the square root to get the crow flies distance which was 1.732 units. We don't really know or care whether these are feet, meters, or anything else, so we just say units. But we need to find the distances for atoms from the other three clusters and then we want to find the same distances for each of the 99 customers. So we need to find an easy way to do that using our Excel skills. Let's start by recreating the basic formula for cluster one for atoms and cell L34 for the first cluster. I'm going to click an L34, click equal, and start entering my formula H2 minus L2 to get the cluster one minus atoms one hit enter. And that gives me a zero, but it only is showing the distance for the first order. I need all the orders, so I'm going to again click an L34, go up here into the formula bar, and when I click inside the formula, it shows me the ranges that are included. I can just put my cursor over that lower right hand corner, get the two-headed arrow, and drag down to include all 32 orders, and I do the same thing over here for atoms and drag down to get all 32 of atoms responses to those offers I should say. So now we've got the basic formula, and I just hit enter. Wow, we get an error message. Well, the reason for that, we're now trying to tell Excel to subtract a range of cells from another range of cells. That's called an array function, an array action. So we need to give Excel heads up that that's what we want. I'm going to click back in that cell again, go up here in the formula bar to access it again. Now I want to use the control, shift, enter, and now I get a zero, which tells me the math is working. Excel is doing the array function, but of course, we gave it a nonsentical formula, and we need to correct that. I want to first start to duplicate that Pythagorean theory, and I want to take these differences and square them. So I'm going to go back in here, click in my formula, click in front of H2, put a opening parentheses after L33, a closing parentheses, put a carrot, and then square to square those. Now to enter that again, I've got to go control, shift, enter, and we've now got the second part of the formula, we've got the square of the difference. Now I need to sum those squared differences, so I click back in that cell again to select it, click up in the formula bar, and I'm going to put my cursor in front of the first parentheses and type in SUM to select the sum function, and I need to put a beginning parentheses and a closing parentheses, and again, control, shift, enter, and that gives me the sum of those squared differences, which is three, as you can see, the square of minus one, minus one, and minus one is one. So I've got three. The final step is to take the square root of that, so I'm still clicking in that cell, go up here into my formula, click in front of the sum, and now I want to enter the SQRT square root function, double click on that, and again, it gives me the opening parentheses and I just need to add a closing parentheses, I believe, and control, shift, enter, yes, that gives me the same answer I had before manually inserting the columns and doing that math. So we've got an array function that gives us the correct value for the distance from atoms to cluster one center. We need to be able to drag that formula down to find the distances for clusters two, three, and four, but first we need to modify it using some absolute cell reference. Before I start editing that formula, I want to get rid of these two columns, M and N, that we inserted temporarily because we don't need them, select them and then delete and then all of my customers move orders, so we've got all our customers adjacent here. That formula works for customer atoms for cluster one. It'd be nice to be able to drag it down for clusters two, three, and four for atoms, and then also drag it to the right for customers allen through the other 98 remaining customers, so we won't have to do a lot of manual editing. But first of all, we need to fix some of the cell references. I'm going to click on that cell to select it and then go up here in my formula and click in H2, which is the cell reference for the cluster. Now, we're going to have to do some manual editing to drag it down to two, three, and four, but I'm going to use the F4 key to lock down that cell reference, and I'm going to go over here to H23 and hit F4 again to lock that down totally. Now, for the customers, we want the row to stay fixed as we drag down and across, but we want the column reference to change to M in NOP. So I just want to lock down the row reference. So I hit F4 once, twice to get the donor sign in front of the row and then an L33 once, twice. And so that's, I think, gotten our formula to the point where we can most easily use it. So we're going, again, to control, shift, enter, and that works. So now I can drag this down, first of all, and of course, I'll get the same value because we were just copying that formula down for atoms. Now, I want to edit this formula for cluster two. I'm going to click on cluster two and then go up here in my formula bar and click on the cell references there. And you can see, again, the ranges that were included. For cluster two, we want this column instead of the H column. So I'm just going to find that upper right corner and get the two-headed error and drag over to select cluster two. And then I want to go to the upper left corner, get the double-headed error and drag over to just get the cluster two column. And again, we go to the control, shift, enter. And of course, we get the same value because we've got all zeros here for the location of the clusters. But we've now changed. You can see that it goes from H to I. And now I want to edit the cluster three. I just click in it again and do that same process. Click in the formula bar and then drag my corner over to cluster three and the second corner over to cluster three. And then hit control, shift, enter. Go into that formula. I'm going to go down here to cluster four. Do the same thing. Click in the formula bar. And then drag over to get cluster four. And here to get cluster four. And everything matches. So control, shift, enter. Now I've got these formulas set up for each of the clusters for atoms. And again, as we start changing the locations of the clusters, these distances will change. But I'm also set up that I can copy this formulas over through the 98 other customers. So I'm going to select those and then just start dragging all of the way to the end, which is there at DG 34. And let go. And I have those formulas successfully copied across. The next step is to determine which cluster to assign each customer. And we will do that based on the minimum distance to a cluster. We want to find the cluster that has the minimum distance, the cluster that is closest to each customer. And of course, right now, they're all the same. But once we start optimizing this, we'll put different values in here for the cluster locations and we'll solve this. First thing we need to do, and I've added two labels down here, minimum cluster differences. And that's just equal, M-I-N, the min function. And we want to find the minimum number of the distances in clusters one, two, three, and four, equal. And of course, right now, they're all the same. The next thing is to use an indexing function for us to tell us once these distances start changing and we get one that is truly a minimum, we'll want to know which of them it is. And we use something called the match function. M-A, click on match. And the first thing is to look up the value, we're going to look up this minimum cluster difference in L38, put a comma. And then it wants to look in this range, comma. And then we're just going to use the exact match, zero. I guess I'll have to enter that, zero, and then close it and hit enter. And it gives the, right now, the assigned cluster one, which is the logical one. But as these start changing, it will tell us which cluster this customer is assigned to. And now I want to be able to drag these formulas all the way down. So I'm just going to highlight those two formulas and drag them down for all of my customers. Keep going, there we go. And so right now, of course, all the customers are assigned to cluster one. We need to figure out how to find the cluster with a minimum distance. Minimum should clue that we're going to use something called Solver to help us find it. But we need to have an objective function for Solver to work on. Over here in the column A, I've put a label in cell A35 called Total Distance. And then in cell A36, we're going to put a formula, which will give us the total distance from each customer and its minimum distance and sum those all up. So I'm just going to hit Equal, S-U-M, and then I want to go down to this first cell here for atoms. And then drag that formula all the way across, and then put my clothing parentheses, hit Enter. And that gives me my current total distance, total minimum cluster distance of 174.63. And then we'll run Solver to help us find the best location for these clusters that will minimize the distance from the clusters to the customers.