In this screencast, I'm going to explain how to make named ranges dynamic. I'm going to explain why you want to do this and what the advantages of doing this are. If you want to work along, I have this in a file called Dynamic ranges. I've got maybe some weights here of some object. We can easily calculate the average by just typing in the average function. I can also type in something known as a standard deviation, which gives us a measure of the variability. We can calculate the maximum of that range, and also the minimum of that range. So a lot of this, perhaps other than the standard deviation you guys were already aware of. You see in here I've referenced A2 to A8. In general, it's a good idea instead of using ranges like that to just name this, and so we can name a range by going up here to the name box, I'll just call this weights. So now the average, it's better in general to use named variables just so you don't get confused. So I'm just going to replace all the ranges I had in these formulas with our named weights array over here, and we're all set. So for example, the minimum we're taking the min of the weights. One of the nice things about named ranges is if we insert, so let's say insert a cell by shifting that down, and maybe I put in a new value here, 29.8, you see that the average, so let me do control Z, you see that the average in all of these things update. So when I do control Y and toggle between, they update. If I go up here to the name box and I type in weights, it updates if you add something in the middle of it. However, one of the main disadvantages if I add something to the bottom here, 31.3, you see that it's not updating that. So let me do control Z, control Y, control Z, control Y, you guys can see that these are not updating. Furthermore, if I type in weights, it's only recognizing those initial ones. Again, I can add in, I can insert, shift them down, and I can add in another value, and that's incorporated into our weights named array. So the question arises, how do I get this last one that's entered, 31.3 to be included in my average standard deviation, max, and min calculations? Well, I'm going to show you how to do that here. In order to do this, it's known as dynamic naming of ranges. We first have to understand something known as the offset function because we're going to use that offset function in the way that we do that. So the important thing about the offset function, it always returns an array. So that's like the most important thing that you need to remember. We always start, the first argument is where you start. So if I were to start at this point, F3, that's our starting point. The second argument is the rows from that starting point that we want to begin our array. So if I wanted to make an array of cells G5 through H6, then I'm going to offset two rows and one column. So starting in F3, if I offset two rows and one column, I'll be starting in G5. If I wanted to output G5 to H6, that would be a two-by-two array. So I can put a 2, 2. The newer versions of Excel, you can just press Enter. Otherwise, what you'll have to do is you'll have to highlight a two-by-two array before you do that, but the offset function yields an array. We're going to use that in our dynamic naming of ranges because we want to rename a range. In fact, in our name manager, we want to rename such that it's a dynamic range, and the way we're going to do that is we're going to incorporate this formula. We're going to use offset, we're going to start at cell A2. In fact, we're going to make that an absolute reference, and we're going to offset by 0, 0 because I want to start in A2, I don't want to offset by anything. Then we're going to create an array. Remember, we're creating the weights vector and it's going to be size. Now, this is the important part. We want to make the size dynamic. So we're going to count the total number of items in column A, and we're going to make sure we're not counting that top one. I'm going to use the count function because the count function ignores text. So I'm going to count and I can do columns A, I'm going to make that an absolute reference. So here, I have 1, 2, 3, 4, 5, 6, 7, 8, 9 and it's not going to count that top one. So the array that's going to be output by this offset formula is going to start in A2, it's going to have a height of nine, and we want to make that a width of one, and so that's what this looks like. You add something to this, you see that it automatically updates down here because we used that count formula up here. So I'm going to go ahead and just copy this entire formula. Make sure you're not just copying down here, but go up here and manually, so highlight that, right-click, copy, and then press Enter. Now I'm going to go up to the name manager, and for weights, I'm going to edit this and here in the refers to, I'm just going to do my control paste. Make sure all the references are absolute, otherwise it tends not to work and then I can press Okay, press Close, and then I'm just going to delete this test array over here. Let's make sure if we delete this 31, you see that it changes, and I can add different elements to here and it's automatically updating. Let's make sure the max updates. If we put a 70, it's updating and if I put a really small number, it's updating there. Oftentimes, you want to use dynamic ranges for various things. We'll encounter a couple more examples in this course. One really cool thing that bypasses the need to do this, it actually does dynamic ranging automatically is when we use Excel Tables, and you'll learn more about that in week 4 of the course. Thanks for watching.
