Excel OFFSET Function for Dynamic Calculations - Explained in Simple StepsJun 17, 2021
OFFSET is an interesting formula, and it's one that can do a lot more than meets the eye. The OFFSET is a way of giving Excel a direction to go. You start by telling it how many rows to move and then how many columns to move to get to its destination. Now, this destination can be a single house, like a single cell. It can be a street like many cells in a row or a column or it can also be like a town, like an area, a range in Excel. Now you might think what the heck can this be useful for?
Why would I need to use an address in my Excel files? In this example, I have months here and sales revenue here and I want to get the average for the last six months. So obviously one way to do this is to use the AVERAGE
functionand then highlight this, like so. But now the problem is that this is a
dynamicreport. So every month, new data arrives for August. I put the data here. In order for this 6 month average to work correctly I have to drag this range down manually and that is something I want to avoid and the OFFSET
functionis what helps me to avoid it.
More Interesting Facts About,
excel offset function for dynamic calculations explained in simple steps...
To show you how we can do this, I'll show you the OFFSET by itself as the first step, and as the second step, we'll build it into the AVERAGE function. The syntax of the OFFSET function is that you always need a starting point, a reference to say before you start walking, where are you going to start? We can choose any cell, but it should be close to the range you want to end up in. So I'll choose this cell and then how many rows do I want to move down? So let's say I want to download one.
How many columns do I want to move? I'm going to put zero here because I want to stay in this column and the last two arguments are the height and the width. A one, one means a cell. These last two can never be a zero, zero. At the very least, they can be one, one. If I wanted to like these three, I would put three, one. Let's go with one, one first and we get 100. So we start here, we go down one, we don't move columns, give us a cell, it's 100. If I move three down, I get 130.
If I move columns, if I actually put a minus one , which means from here, go here, I'll get my date back. I would only have to change the format. If I do a plus one I get a zero because where am I? I'm here. Let's put it, look, I'm here. That's basically what OFFSET does. If I wanted a range, I'm going to put this column back to zero and instead of having a one for the height, I'm going to put three for the height. I get value because what it does is I'm starting here, going down three, one, two, three and then I'm given these three cells.
So you can't actually put these three cells in one cell. Now whenever you use OFFSET such that these last two arguments are ranges, return not just a cell but a range of cells, you need to wrap your OFFSET formula in a formula that can handle ranges. What would that formula be? For example, the average formula or the sum formula or the count formula, anything that can handle ranges. So in this case, we're going to use the average formula. 108. So that would be the average of these three. Now let's extend this formula to get the last six months.
There are different ways to write this. One way is you can first see what the last cell is here using the COUNT function and then go down to the last cell and then as its height you go back and highlight the minus six or six rows before this or you basically find your last cell and then you go back to the sixth row and your height is then plus six. Don't be confused by this. I'll just show you as a first step, the
simplest one is the one I prefer and then the other one too. Later, you can also play with it and see how it works.
As the first method to write this, now I start here, right? I need to know how much I should lower? I can do this by counting the number of filled cells I have here. I can use here the COUNT function or the COUNTA function. COUNTA basically counts even if it's text. The COUNT only only counts if these are values. In this case, it doesn't matter. COUNT or COUNTA and then I highlight this and the important thing is that you highlight more of what you have because in the future I'm going to have data here. So I have to take them into account as well.
The result of this COUNTA function will be seven. He is going to tell me to go down seven, which is one, two, three, four, five, six, seven. I'm done here, how many columns do I have to move? Nothing, I want to stay in this column and what is my height? That's where minus six comes in. Because I want the average of the last six months, minus six will be this, from here to here and the one is that is my width. 113. Let's check that out. 113. So now when I add a new month to this, it's one to seven.
One to seven. My rank is updated automatically. You can also check this by going to formulas, evaluate formula. I start here, under eight, I don't move any columns, minus six, one. So the final range you're taking the average from is B6 to be B11, which is this to this. The other way of writing this, as I mentioned before, I'll show you, is average compensation. Now we can start here in this case. the number of rows I want to go down is I'm going to do a COUNTA again, or let's do a COUNT now. Highlight this and I do a minus six.
Basically, the result of the COUNT formula in this case will now be eight because I added another month, minus six, that's two. Just go down two rows, one, two. For my columns, it is zero. I don't want to move any columns and for my height, I don't do minus six but plus six and my width is one. I understand that, which is the last six months. Two different ways of writing this lead to the same conclusion. Now, what if your data was organized like this? So we want the average of the last six months but we have them in rows.
We do the same. I start with the AVERAGE function, then the OFFSET, and my reference is here. Let's say I want to start here. How many rows do I want to move? Any. So that's zero. How many columns do I want to move? Again, that depends on how many columns you have numbers. I'm going to use the COUNT function or the COUNTA function and highlight as far as I think I'm going to have numbers. Now for the height, it should just be one now, but for the width, I need from here to go here, I need it to be minus six.
Okay, it was the other way around, but you do the same thing to get the average of the last six months. So if I add data here for August, I get from one to seven. So be creative and practice this in all directions until you get the hang of it. Now another good example of this is if you have your months and your sales revenue and you have a report where you always want the average for the next three months based on a selection, let's say you've included a dropdown here where you can select your month from this list and want to get the average for the next three months.
We are going to do the same. So we're going to use the AVERAGE function together with the OFFSET. As our point of reference, that can be fixed. We can always start from here and then how many rows do we want to go down? Well, in this case, we want to move down to four-14, which is here. We want to go down that much. What function can I use that returns a number? The MATCH function because that gives us the 90th position in this list and the result would be the fourth position. So I know I need to move down four rows.
First I need to have to fetch the value. I'm looking for this here. I want a perfect match, never forget it. That's the number of rows I want to move down because the result of this is four. One two three four. Now for the columns, I don't want to move any columns. I want to stay in this column, so it will be zero. For the height, that depends on my question. If I wanted to include this month as well, I would put a three and a one for my width. 102, so let's check that out. That's this, these three, that's 102.
If I didn't want to include this month, I just have to add one more to my match. So I can say find this, then download an extra one. That would be the three months after this. So these would be. This is purely
dynamic. So if you were to go down and select December 2014, you would get two, three, two. In this case I'm doing the next three months, the three months after this. It would be these three, two, three, two. This is how you can use the AVERAGE function, the OFFSET function, and the MATCH function all together to create this dynamic effect.
One word of caution is that OFFSET is a volatile formula, which means that it calculates every time you make a move in Excel. Now, to be honest with you, on files I've used OFFSET on, I've never experienced my files getting slow due to using OFFSET, but I haven't overdosed on them either. So I also recommend that you do not overuse them.
If you have any copyright issue, please Contact