All language subtitles for 6. The OFFSET Function

In this case, we're going to explore how the offset function works, and offset is a function that

you'll see quite often used on its own.

But a lot of the time you'll see it combined with other functions to produce some really powerful results.

So what exactly is the offset function and how does it work?

Well, we're going to take a look at a basic example, first of all, and then I'm going to show you

a more practical way of using Offset.

Now the data that we're using in this first example is just some basic sales data for different regions

across different months.

And what the offset function will basically allow you to do is return a result from a cell that is a

specified number of rows or columns away from your starting cell.

Now, that is really hard to picture without seeing an example.

So let's just click somewhere over here and type in equals offset.

Now, notice here that we have five arguments, and the last two are optional arguments because they're

in those square brackets.

Now the first piece of information, this is the most important piece of information when you're using

the offset function is the reference.

You always need to have a starting point, a starting cell, and that cell is used as your reference

point.

Now, which cell you choose to use as a reference is entirely up to you.

But for the purpose of this example, I'm going to use Cell a three comma.

The next argument is rows.

So what I can say here is I can get this function to move down whatever number of rows I specify.

So if I want to, for example, let's say if I want to return the result for the North Region for March,

I need to move down three rows comma.

And I need to go across one column, comma, I can then choose the height and the width of my range.

So for example, if I choose a height of one and a width of one, that's basically means one cell.

If I was to choose a height of two and a width of two, that would be a selection of four cells.

So if I close this bracket now and hence, I should find that the result I get returned is the march

value for the North region.

And if I take a look at that just there, that is exactly what I've got.

So you specify your starting point, you specify how many rows you want to move down, how many columns

you want to move across and then basically how many cells you want to select from that point.

That can be one cell, or it can be multiple cells, a range of cells.

So let's do it again and take a look at what happens if we select a range of cells.

So equals offset.

I'm going to use the same reference point a three.

This time, let's say I want to return the July figures for the south region and I want to basically

return July to December.

So rows, how many rows do I need to go down?

Well, one two three four five six.

I need to go down seven rows to get to July.

I want to return them from the south region, so I need to go across two columns now because I want

to return all of the results from July to December.

The height is going to be one two three four five six cells effectively.

But I only want to return them from one column, so it's going to be one for the width.

Close the bracket, Hansa and it's going to return.

All of those results, though, should exactly match what we have just there.

Let's do it one more time, so you get the hang of this equals offset.

Let's choose a different reference point this time let's to sell a three comma.

How many rows do I want to go down this time while I'm going to select a random region in the middle?

So let's go down to the April value for the east region.

So I need to go down one to three four rows now I need to go back one column.

So for this, we can use minus values if we need to go back.

So minus one, I can then specify my column height and my column width.

Now I'm going to say I want, let's just do two.

And to close the bracket, let's see what we get.

So it's bringing back this range.

Just here, we moved down four rows.

We moved back one.

And then we said that we wanted a range that was two cells in height and two cells in width.

So that is basically how the offset function works.

Now you might be thinking to yourself, OK.

I get it.

That's totally fine.

But how is this function useful to me?

Well, let's take a look at a practical example.

Now, if we go across to this next spreadsheet, you can see here I have an offset formula and I've

combined it with a sum function.

And that's what I said.

You quite often see Offset combined with other functions to create a really powerful formula.

Now what it's done here is it's looking at my little table and it's summing together the last three

months of sales figures.

So basically, these figures, just they're being added together to produce this result.

Now, why would we use Offset just here?

Well, it might be that with this data every month, new sales figures get added to the end.

And if I was to add another month on the bottom here, let's just drag this down and let's say 1500.

Notice that the sales figure has now updated to $4000.

So I've added a new value on the end.

It's still looking at the last three months.

If I select these cells, you can see down in the status bar.

The sum is actually four thousand.

So it's adjusted the positions once I've added a new value onto the bottom and I haven't had to change

anything in the formula.

Now it's done that because I'm using the offset function, so I'm going to undo just to get rid of those

values.

And let's go through how we constructed this.

Now I'm combining offset with some because I want to basically add up the last three months now for

this example.

Let's do the last six months.

That's what I want it to add up.

So we need to start this calculation with a sum function and then go straight into our offset.

So now we have our arguments.

Let's choose a reference point.

I'm going to choose Cell B three comma.

We now need to specify how many rows we want to count down.

Now, if I was just to put something in here like five, this formula isn't going to work because that

number is hardcoded in.

It's not going to adjust its position when I add new data onto the bottom.

So here, in order to find the rows, we need to do a count a and we want to count all of the values

in this range before to be fifteen.

Comma.

Now I don't want to move across any columns, so I'm going to put a zero in here and then the heights,

well, I want the last six months of data.

So we're going to go from the bottom because we want the last six months so effectively minus six needs

to go in here.

And then our last argument is with.

Well, it's the width of one column.

So we want to have a one on the end here.

Let's close off our offset and close of as some and enter.

So I'm getting a result of 6300.

And if I check that simply by highlighting the last six months of values, so one two three four five

six.

Let's take a look in the status bar.

That's the quickest way.

The sum calculation there is 6300, which matches my result in Cell E four.

Now let's see what happens if I add some more data onto the bottom of this table.

So I'm just going to add another month.

Let's add a value of 2000 in there.

Now, notice that this formula hasn't automatically updated to include that new value, because now

the last six months, the sum of those six months should equal 7500.

A Mirasol is still showing the old total.

Now I can see that I've got a green triangle in the corner, so what I could do is click here.

Click the triangle.

And I could update the formula to include the new cells.

That is one way of fixing this problem.

The other way that I could fix this would be to modify this formula so that my cell range extends past

the last value that I have in here.

So instead of B four to be 15, I might change this to something like B 23.

So let's go up to the Formula Bar and change B 15 to be 23.

Now, if I enter, I get the correct result because my new result is being included in the formula.

Another way of doing this would be to use a table.

So if I click in my data center, control a control t, yes, my table has headers, let's click on

OK and you can obviously change the formatting of that table if you want to.

What I can then do is add another month and the table auto expands to accommodate that.

So when I add in the new total, you can see that now that total automatically updates, let's just

double check that that is exactly the last six months.

And if I look down in that state, spar that sum total, yes, ten thousand six hundred.

So you have three different methods there to get offset to update automatically, you can click on that

warning triangle.

You can extend the formula past the end of your data, or you can put your data set into a table.

So that is a practical use of offset with the sum function.