OFFSET is an interesting formula and it's one that can do so
much more than meets the eye. The OFFSET is a way of giving
Excel an address to go to. You start off 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 one row or one column or it can
also be like a town, like an area, a range in Excel. Now you might think what on
earth can this be useful for? Why would I ever 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
of the last six months. So obviously one way of doing this is to use the AVERAGE function
and then highlight this, like this. But now the problem is that
this is a dynamic report. So every month, new data
comes in for August. I put in the data here. To get this average of the last
six months to work properly, I have to drag this range down manually and that's something I want to avoid and the OFFSET function is
what helps me avoid this. To show you how we can do this, I'm going to teach you OFFSET on its own as a first step and as a second step, we're going to integrate 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 your walk, where are you going to start? We can pick any cell but it should be close to the range that
you want to end up in. So I'm going to pick this cell and then how many rows
do I want to move down? So let's say I want to move down 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 one cell. These last two, they can
never be a zero, zero. At the minimum, they can be a one, one. If I wanted like these three,
I would put three, one. Let's stick with one, one first and we get 100. So we start here, go down one, don't move columns, give
us one cell, it's 100. If I would move down three, I get 130. If I move columns, if I
actually put a minus one, so meaning from here, go here, I get my date back. I would just have to
change the formatting. If I do a plus one, I get a zero because where am I? I'm here. Let's put it to, see, I'm here. That's basically what OFFSET does. If I did want a range, I'm going to put this column back to zero and instead of having a one for height, I'm going to put three for height. I get value because what it
does is that I'm starting here, moved down three, one, two, three and then give me these three cells. So it can't actually put
these three cells in one cell. Now, whenever you use OFFSET
in a way that these last two arguments are ranges, they're
not just returning one 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 we're going to expand on this formula to get
the last six months. There are different ways of writing this. One way is that you can first see what's the last cell in here by
using the COUNT function and then come down to the last
cell and then as your height, you kind of 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 get confused by this. I'm just going to show
you as a first step, the simpler one is the one I prefer and then the other way as well. Later on, you can also play around with it and see how they work. As the first method of writing this, now I start here, right? I need to know how much
should I move down? I can do this by counting the number of filled
cells that I have here. I can use here the COUNT
function or the COUNTA function. COUNTA basically counts even if it's text. The COUNT alone 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 than what you have because in the
future, I will have data here. So I need to take them
into account as well. The result of this COUNTA
function is going to be seven. It's going to tell me move down seven, which is one, two, three,
four, five, six, seven. I end up here, how many columns to move? Nothing, I want to stay in this column and what's 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's my width. 113. Let's check that. 113. So now when I add a new month to this, it's one to seven. One to seven. My range updates automatically. You can also check this
by going to formulas, evaluate formula. I start off here, I go down eight, I don't move any columns, minus six, one. So the final range that
it's taking the average of is B6 to be B11 which is this to this. The other way of writing
this, like I mentioned before, I'll just show it to
you, is average offset. 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. So basically the result of the COUNT formula in this case
now is going to be eight 'cause I added another
month, minus six, it's two. It only moves down two rows, one, two. For my columns, it's zero. I don't want to move any columns and for my height, I don't do a minus six but instead a plus six and my width is one. I get that, which is the last six months. Two different ways of writing this, you come to the same conclusion. Now, what if your data was
organized in this way instead? So we want the average
of the last six months but we have them in rows. We do the same thing. I start off with the AVERAGE function, then the OFFSET and my reference is here. Let's say, I want to start off here. How many rows do I want to move? Nothing. So that's zero. How many columns do I want to move? Again, that depends on how
many columns I have numbers in. I'm going to use the COUNT
function or the COUNTA function and I'm going to highlight until where I think I'm going to have numbers in. Now for height, It should just be one now but for width, I need from here to go here, I need it to be minus six. Okay, so it was just the other way around but it does the same thing of getting the average of the last six months. So if I add data here for August, I get one to seven. So just be creative and practice this in all directions until
you get the hang of it. Now another good example on this is if you have your months
and your sales revenue and you have a report
where you always want the average of 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 you want to get the average
of the next three months. We're going to do the same thing. So we're going to use the AVERAGE function together with OFFSET. As our reference point, that can be fixed. We can always start off from here and then how many rows
do we want to go down? Well in this case, we want to move down until we
get to four-14, which is here. We want to move down this much. Which function can I use
that gives me back a number? The MATCH function because
that gives us the position of 90 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 lookup value. I'm looking this up in here. I want a perfect match, never forget that. That's how many 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 that's going to 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. That's this one, these three, it's 102. If I didn't want to include this month, I just have to add a plus one to my match. So I can say find this,
then go down one extra. That would be the three months after this. So that would be these. This is purely dynamic. So if I would go down
and select December 2014, I 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. That's how you can use
the AVERAGE function, The OFFSET function and the MATCH function all together to create
this dynamic effect. A word of caution is that
OFFSET is a volatile formula, which means it calculates every time you make a move in Excel. Now to be honest with you, in the files that I've used OFFSET in, I've never experienced my
files to become slow because of me using OFFSET but I
also don't overdose on them. So I also recommend that
you don't overuse them.
