馃攳
Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps - YouTube
Channel: Leila Gharani
[2]
OFFSET is an interesting formula
[4]
and it's one that can do so
much more than meets the eye.
[8]
The OFFSET is a way of giving
Excel an address to go to.
[12]
You start off by telling it
how many rows to move and then
[16]
how many columns to move
to get to its destination.
[20]
Now this destination
[22]
can be a single house, like a single cell.
[25]
It can be a street like
many cells in one row
[29]
or one column or it can
also be like a town,
[33]
like an area, a range in Excel.
[37]
Now you might think what on
earth can this be useful for?
[41]
Why would I ever need to use
an address in my Excel files?
[47]
In this example,
[48]
I have months here and sales revenue here
[51]
and I want to get the average
of the last six months.
[56]
So obviously one way of doing this
[59]
is to use the AVERAGE function
and then highlight this,
[64]
like this.
[66]
But now the problem is that
this is a dynamic report.
[70]
So every month, new data
comes in for August.
[74]
I put in the data here.
[76]
To get this average of the last
six months to work properly,
[80]
I have to drag this range down manually
[84]
and that's something I want to avoid
[87]
and the OFFSET function is
what helps me avoid this.
[97]
To show you how we can do this,
[99]
I'm going to teach you OFFSET on its own
[101]
as a first step and as a second step,
[104]
we're going to integrate it
into the AVERAGE function.
[109]
The syntax of the OFFSET function
[112]
is that you always need a starting point,
[114]
a reference to say before
you start your walk,
[117]
where are you going to start?
[119]
We can pick any cell but it should
[121]
be close to the range that
you want to end up in.
[124]
So I'm going to pick this cell
[126]
and then how many rows
do I want to move down?
[129]
So let's say I want to move down one.
[132]
How many columns do I want to move?
[135]
I'm going to put zero here
[137]
because I want to stay in this column
[140]
and the last two arguments
are the height and the width.
[145]
A one, one
[147]
means one cell.
[151]
These last two, they can
never be a zero, zero.
[156]
At the minimum, they can be a one, one.
[159]
If I wanted like these three,
I would put three, one.
[163]
Let's stick with one, one first
[166]
and we get 100.
[167]
So we start here, go down one,
[170]
don't move columns, give
us one cell, it's 100.
[175]
If I would move down three,
[177]
I get 130.
[181]
If I move columns, if I
actually put a minus one,
[186]
so meaning from here, go here,
[190]
I get my date back.
[193]
I would just have to
change the formatting.
[195]
If I do a plus one,
[198]
I get a zero because where am I?
[202]
I'm here.
[203]
Let's put it to, see, I'm here.
[209]
That's basically what OFFSET does.
[212]
If I did want a range,
[215]
I'm going to put this
[218]
column back to zero
[220]
and instead of having a one for height,
[223]
I'm going to put three for height.
[227]
I get value because what it
does is that I'm starting here,
[232]
moved down three, one, two, three
[235]
and then give me these three cells.
[237]
So it can't actually put
these three cells in one cell.
[244]
Now, whenever you use OFFSET
in a way that these last two
[248]
arguments are ranges, they're
not just returning one cell
[253]
but a range of cells, you need to wrap
[256]
your OFFSET formula in a
formula that can handle ranges.
[262]
What would that formula be?
[265]
For example, the average formula
[267]
or the sum formula or the count formula,
[269]
anything that can handle ranges.
[272]
So in this case, we're going
to use the average formula.
[277]
108.
[278]
So that would be
[281]
the average of these three.
[286]
Now we're going to expand
[287]
on this formula to get
the last six months.
[293]
There are different ways of writing this.
[295]
One way is that you can first see what's
[298]
the last cell in here by
using the COUNT function
[303]
and then come down to the last
cell and then as your height,
[307]
you kind of go back and highlight
[309]
the minus six or six rows before this
[314]
or you basically find your last cell
[317]
and then you go back to the sixth row
[321]
and your height is then plus six.
[326]
Don't get confused by this.
[327]
I'm just going to show
you as a first step,
[329]
the simpler one is the one I prefer
[332]
and then the other way as well.
[333]
Later on, you can also play around
[335]
with it and see how they work.
[339]
As the first method of writing this,
[341]
now I start here, right?
[345]
I need to know how much
should I move down?
[348]
I can do this by counting
[351]
the number of filled
cells that I have here.
[355]
I can use here the COUNT
function or the COUNTA function.
[360]
COUNTA basically counts even if it's text.
[363]
The COUNT alone only
counts If these are values.
[367]
In this case, it doesn't matter.
[368]
COUNT or COUNTA and then I highlight this
[372]
and the important thing is
that you highlight more than
[376]
what you have because in the
future, I will have data here.
[381]
So I need to take them
into account as well.
[385]
The result of this COUNTA
function is going to be seven.
[391]
It's going to tell me move down seven,
[392]
which is one, two, three,
four, five, six, seven.
[395]
I end up here, how many columns to move?
[399]
Nothing, I want to stay in this column
[402]
and what's my height?
[404]
That's where minus six comes in.
[406]
Because I want the average
of the last six months,
[409]
minus six will be this, from here to here
[413]
and the one is that's my width.
[418]
113.
[420]
Let's check that.
[422]
113.
[424]
So now when I add a new month to this,
[431]
it's one to seven.
[435]
One to seven.
[437]
My range updates automatically.
[440]
You can also check this
by going to formulas,
[443]
evaluate formula.
[448]
I start off here, I go down eight,
[452]
I don't move any columns, minus six, one.
[453]
So the final range that
it's taking the average of
[457]
is B6 to be B11 which is this to this.
[465]
The other way of writing
this, like I mentioned before,
[469]
I'll just show it to
you, is average offset.
[474]
Now we can start here in this case.
[477]
the number of rows I want to go down
[480]
is I'm going to do a COUNTA
again, or let's do a COUNT now.
[485]
Highlight this
[490]
and I do a minus six.
[494]
So basically the result of the COUNT
[496]
formula in this case
now is going to be eight
[500]
'cause I added another
month, minus six, it's two.
[505]
It only moves down two rows, one, two.
[511]
For my columns, it's zero.
[513]
I don't want to move any columns
[514]
and for my height, I don't do a minus six
[518]
but instead a plus six
[522]
and my width is one.
[527]
I get that, which is
[531]
the last six months.
[535]
Two different ways of writing this,
[537]
you come to the same conclusion.
[542]
Now, what if your data was
organized in this way instead?
[547]
So we want the average
of the last six months
[550]
but we have them in rows.
[552]
We do the same thing.
[554]
I start off with the AVERAGE function,
[556]
then the OFFSET
[559]
and my reference is here.
[561]
Let's say, I want to start off here.
[564]
How many rows do I want to move?
[566]
Nothing.
[569]
So that's zero.
[571]
How many columns do I want to move?
[573]
Again, that depends on how
many columns I have numbers in.
[579]
I'm going to use the COUNT
function or the COUNTA function
[584]
and I'm going to highlight until where
[587]
I think I'm going to have numbers in.
[593]
Now for height,
[595]
It should just be one now but for width,
[599]
I need from here to go here,
[603]
I need it to be minus six.
[611]
Okay, so it was just the other way around
[614]
but it does the same thing of getting
[617]
the average of the last six months.
[620]
So if I add data here for August,
[626]
I get one to seven.
[632]
So just be creative and practice this
[636]
in all directions until
you get the hang of it.
[642]
Now another good example on this
[645]
is if you have your months
and your sales revenue
[649]
and you have a report
where you always want
[652]
the average of the next three
months based on a selection,
[657]
let's say you've included a dropdown here
[659]
where you can select
your month from this list
[663]
and you want to get the average
of the next three months.
[668]
We're going to do the same thing.
[670]
So we're going to use the AVERAGE function
[673]
together with OFFSET.
[676]
As our reference point, that can be fixed.
[679]
We can always start off from here
[683]
and then how many rows
do we want to go down?
[687]
Well in this case,
[687]
we want to move down until we
get to four-14, which is here.
[692]
We want to move down this much.
[695]
Which function can I use
that gives me back a number?
[702]
The MATCH function because
that gives us the position
[707]
of 90 in this list
[710]
and the result would
be the fourth position.
[713]
So I know I need to move down four rows.
[720]
First I need to have to lookup value.
[722]
I'm looking this up
[726]
in here.
[729]
I want a perfect match, never forget that.
[733]
That's how many rows I
want to move down because
[736]
the result of this is four.
[739]
One, two, three, four.
[742]
Now for the columns, I don't
want to move any columns.
[745]
I want to stay in this column,
so that's going to be zero.
[750]
For the height, that
depends on my question.
[754]
If I wanted to include this month as well,
[756]
I would put a three
[759]
and a one for my width.
[765]
102, so let's check that.
[767]
That's this one, these three, it's 102.
[772]
If I didn't want to include this month,
[775]
I just have to add a plus one to my match.
[779]
So I can say find this,
then go down one extra.
[783]
That would be the three months after this.
[787]
So that would be these.
[793]
This is purely dynamic.
[795]
So if I would go down
and select December 2014,
[800]
I get two, three, two.
[802]
In this case I'm doing the next
[805]
three months, the three months after this.
[809]
It would be these three, two, three, two.
[817]
That's how you can use
the AVERAGE function,
[820]
The OFFSET function and the MATCH function
[823]
all together to create
this dynamic effect.
[829]
A word of caution is that
OFFSET is a volatile formula,
[833]
which means it calculates every time
[836]
you make a move in Excel.
[838]
Now to be honest with you,
[840]
in the files that I've used OFFSET in,
[842]
I've never experienced my
files to become slow because
[847]
of me using OFFSET but I
also don't overdose on them.
[851]
So I also recommend that
you don't overuse them.
Most Recent Videos:
You can go back to the homepage right here: Homepage