I'm going to give you a pretty big hint on the top hat consolidator project. This is the only hint that I'm providing for the top hat consolidator project. In this project as one of the requirements, you're going to have to calculate the average of columns for different students. So you have grades for different students and these are different assignments going across the top. I've just got this example here, column-wise averaging. There's no easy way to dynamically average, at least in what I've been able to find. You can use the average function to calculate the averages of these columns of the data. But this doesn't dynamically update, you have to drag this formula over. If I want to add in a new column here like F, those values, it doesn't automatically update. I'm going to show you a way that we can dynamically average columns of a dynamic array. So if we add in new rows and new columns, it should automatically update to make a dynamic array. So I'm going to go ahead and delete this. The first thing I'm going to do is I'm going to convert the data here, I'm going to make this a dynamically named array. To do that, I can go up to the "Formulas tab." I'm going to "Define Name" and I'm just going to call this data. We can use the offset function to do this. I'm sure at this point in part three of the course, you've done this at least once or twice. So I can click on cell before we're going to offset zero rows, zero columns. The number of rows of data is going to be count A of column B minus one. The number of columns of this array. We can do count A, I'll just do row three. When we do this, we can make sure that it's referring to our data up there, which it is. The nice thing about this, so what we've done is we've converted just a static array here to a dynamic array. If the user adds different data to this, as in a new row, we automatically update that, and if we add in a new column, it also automatically updates. We've converted a static array to a dynamic array. Now let me show you how we can compute the average of each of these columns in a dynamic fashion. Now we can do this by using some matrix multiplication. So check out what happens if I make an array of all ones. I have five ones, and this corresponds to the number of rows in our array. You can use the "MMULT function." I can multiply this vector of all ones with our entire vector that we're trying to column-wise average, and this will actually calculate the sum of each of the columns. Now to calculate the average, I can just go back into my original formula here and I can divide by the rows of our array, and that's just essentially our data array. So I can just type in data. Rows of data in this case is five. So we're going to sum the columns and divide by five. So this is a pretty slick way that we can column-wise average an array that can be dynamically updated. Again, if I wanted to add in a couple more items here, I could and they would dynamically update. The last thing I want to show you is how we can make this. So this vector of ones is not dynamic, but we can just do that. So let me just delete this. I'm going to use the sequence function. We can use the sequence function. This is going to be a single row. The number of columns of this array of all ones is going to be the number of rows of data. We can use the rows function of data. Now the start value is going to be one. We can use zero as the step value, so it's just going to be a bunch of ones. So that's how we can end up with a row of all ones and again, we're using the "MMULT function" of that array of all ones, we multiply that by our original data, which is up here and when we divide that by the number of rows of the data, we end up with the average. This dynamically updates. So we can put in a couple of different values here, and it's automatically updating. The one thing we need to do though, to get this to automatically update if we add in a new row is instead of I16 to M16, we just need to turn that into I16 spill operator. Now we can go ahead and add in a new row of data and it automatically updates. I want to show you a second option. So let me just go ahead and delete some data here. Let's just start over. Another option is you can convert this into an Excel table so we can "Format As Table." We can do that. We can do the same thing that I just did over here. This is a static array. We can convert this into a dynamic array by just typing out the name of the table. In my case, that's Table 2. Now we've got a dynamic array here. We can use the sequence function that's going to be one row and it's going to be the number of rows of our Table 2, that's the number of columns that we're going to have. We're going to start with one. We're going to step size of zero, and now simply I can take the "MMULT function". We're going to multiply J13 with a spill operator with Table 2, and when I do that, we're adding those columns of Table 2 and we can simply divide by the number of rows of Table 2. This automatically updates. I can add in some data here. I can add in a new column. It's automatically resizing and dynamically updating if we add in new rows and columns. So both of these ways can be used for the top hat consolidator project. I think this will really help you. Good luck.
