Hi friends. In this bonus video session we will be talking about offset. Now this is quite often used in financial modeling. And this is slightly tricky. In fact, it took me most time to learn this particular formula, but I will try to make it easy and simple. Let's see.

Now I have a data which talks about sales cost profit for the different years. So I have a basically two by two matrix data on which we can apply v lookup with match for just a quick recap what we when we used to apply, we've come up with match. That is, if I want sales 2008 since 2008, you could have applied v lookup, look for that value, which is given vertically, the one value which is already given as input out here. Now the table array, I have chosen the entire data. And I'll press f4 comma to proceed in the column index number all I can count one, two and three because in the third column lies my data. But let me write a match formula.

Now. This Something that I've already discussed in the previous video. So this is just a quick recap. So match lookup value talks about that, if you remember Jr, follow sr, the analogy that we use to use lookup for that value which is given in the header. So that's your comma, and I must not start using from here, it must be Junior follow Sr f4, comma zero bracket closed. And since this data is not a slap, it's simple sales cost and profit.

I'll be using false for an exact match. Now why did I use v lookup at March, this is just to set the pre mind with respect to offset. Let's say at this given point in time, I would want to reach sales of 2008 that is 1450. The one that I used match for we look up that match for let me highlight that particular value. Now, if you were standing on this cell, and you will have to reach to the yellow cell, how would you do? You would move one row down and you will Jump one column and two columns.

So basically one row down and two columns, right. So what offset does is it selects a reference, which means your starting point a cell. It asks you how many rows, we want to move down, I said to one, and how many columns to the right. Now these are two optional scenarios, optional values, which I'll be talking about the next subsequent cases. But as of now that we close the brackets here, and let's see what I get upset. Now, of course, this you have to put in the numbers, right?

So if I change the number, let's say Rule number two, which will point to one rule below that, which is 1750. And that's exactly what I get. So offset at a very basic level, it means a starting point chosen and how many rows down or how many columns to the right. A question might arise that if I had chosen different Friends as let's say, not particulars, but let's it 2009 good dancer would have changed? Oh, yes. Since the starting point is 2009.

So two rows down, which means this particular row and two columns, right, which means 1700. So now let's see what are the applications of offset? This was a very basic example. And let me start the application from case two. All right. Now what we look up with match couldn't do was it couldn't give you a cumulative set of values.

That is, you could have got any one value based on whether it's sales cost profit, or which year but let's say if the question says that you would want to have sales for all the years till 2008. Let's say both of them. The how do you do that? So for cumulative raw values, I will be using the offset. Let's say at this given point in time, if you were to write a basic sum formula, you would have done something Like this. So, somehow I can automate this selection of reference that is based on how many years has to be taken, I can use some formula.

So to automate this part of the sum formula that is a selection of the range, I will be using offset along with the two extra parameters, which I did not discuss in case one. Let's say at this given point in time, if you were to calculate the total sales from 2007 and till 2008. And if you were starting from particulars, where would you start counting the numbers from one row below, one column to the right that means this is my starting point, one row below and one column to the right. And height would be only one right because you are choosing just these not these two. So height is one. And with with eyes, I can see clearly it's two.

All right. Now if I were to write equal to some Please note, I'm including the offset formula in some Why? Because let's assume if you were to write something like this would give an answer, nope. This is a common mistake. Hence, let me first include that in some formula to let me write some. And then let me write offset.

Alright, right. The opposite is asked for a reference. How many rows below? One? How many columns to the right where you must start counting the number. So I'm just placing my cursor till here by using this one in one.

And then what is the height that I'm looking at? That is only one. And what's the width? Two? If I close double brackets into 2850, let me quickly check some of these two should be 2850. That's correct.

Now you will ask me you know who's going to count this every time especially in a big data? Agreed he would not be Counting this 112 and so on, you would be taking the help of the formula match to do the counting, for example, that is, had it been profit in the question, you would have moved how many rows down? One, two and three, isn't it? How did you count that three by counting within this data the one which is blinking that means this particular row selection must be dynamic data which must be counted with the help of match. Now, in all these scenarios, since it is asking for a cumulative value that means from 2007 to 2008 2007 to 2009 to 2007 to 2010, or 11, it must start from the first column that is the counting. So, this is a static data, I'm just writing a static data which must be fed in by hand.

Now, the height height would remain one all throughout because if it is sales one height, if it is cost, one height, if it is profit, one height This is a static data, at least in this case, the width depends on how many years do you want to calculate the summation for? Again, it's the dynamic data, and you must use a match formula to automate it. So how did you count that which should be two by looking at 2008? And then you counted one and two, isn't it? That means you are counting through match. We'll begin from here from 2007.

To 2011, we are counting for sales cost of profit will begin starting from here below the particular so unlike we look up with match, there is no Junior follow senior, everybody minds his own business that is this mind his own business, this minds his own business, there is no overlap that we witnessed in V lookup with match. So now, if I look at the formula, which part of these I must automate, let me write the formula here this time. So I say sum. All right. offset the reference and just keeping this particulars as the one I used before. Now rules I could have chosen one year but again I must make this automatic right using match.

So let me go back, let me write a match formula. Mm. Now within match formula, let me look up for how many rows below I must start from So, for doing that, let me look up for sales. And then let me count the numeric position of sales. Let me press f4 comma zero. Now, this is just to get the row number from where I must start.

So, for example, sales I must go one row below particulars for cost I must go to rows below the particular section and for profit, I must go to rows so, that is exactly what it is counting the match formula. So, we are still in rows. Let me put comma and let me put digit to one That's a static data that we've already discussed. Putting a comma I'm being asked for height again, let me put one which is a static data, but the last one with that is not static for that. Let me again use a match formula for match. And what how would I decide what is a weight?

By looking at how many years total I must require, right? So look up for the year. Start counting the position number f4, comma zero bracket closed, one more bracket close. So if you are counting 2008 in this green border area, you'd get to an exactly that is what I want for get to. Let me close one more bracket and Let me press enter and see what I get. Yes, 2850.

That's exactly the answer and the magic is 2009. If I write 4350, it takes a cumulative total for these three aspects. If I take let's say, cost, till 2009. So these three numbers are Up to 3900 if you simply calculate that, so that's exactly how I learned the offset formula, of course, break it down into four different components, do it manually, at least initially, and then see which of them are supposed to be static. If they are not supposed to be static, then use match. And here jr follow seniors the one that we used in match we look up with match doesn't follow here, you have to notice based on the different selection below particulars, and we go to case three, and this will solidify the concept of offset.

Let's see in this case, the values are given not in terms of sales cost or profit, but in terms of the different months sales, okay? And whenever you choose the year 2008 and you choose the month, which means February. So from Jan to fab, this total should be calculated. Now has this been let's say March, then you would have given Jan to fab sorry, Jan to March, all the total data is 4800. Now how do I do this? Let me press Ctrl Z for February, I would want to select these two array selection for my sum formula.

So, if I were to do it manually add, I will be starting from the reference point months, how would I proceed? for this? You must start from the first true why because either you calculate only Jan or Jan FEHB or Jan fed much right in all these cases, you're starting from the first through one row below the month column. Now, this column is not static, why because we are jumping ahead based on the years so if it is let's say 2007 you would have skipped one column and then started counting from here. If it is 2008, you would have jumped two columns and then started counting from here. So this is not static.

This is dynamic based on match at least in this case, it is column number two, the height height is dependent on how many months Do you want if you want two months then it is has to be two every January March then the height must be three. So in this case, it is match which is going to be helping you With in all the cases, it's just one based on that here, so that's against static. So if I were to write this using a match formula and offset, let's see how it is moving ahead, so some very important that you must write the sum first, then the offset. So let me proceed reference is months, comma, how many rows below, I can write to one. That's a static data that we've already discussed. Comma, the next is columns for which I must write match formula.

You can also write it this way. That is you must complete the structure of the formula and then we will fill in the match parameters. So we have talked about reference, we have talked about rules, which is static. We've talked about columns, we'll fill this parameters in some time. Now height, height, again requires another match formula. It'd be like MIT and then press TAB key.

Close the brackets, comma. Now which again, which is static So, let me close the brackets once once more. So, this way this is how it is supposed to look and let me now fill in the numbers. So, the match first match is substituted for what four column right that means how many columns must you jump to start calculating the result? Now, that column jumping exercise, how would you know based on the year that has been asked? So, if it is 2007, you would jump one column if it is 2008, you would jump to column right.

So match please look up for the year. In this data region, the one that is highlighted, Let me press f4 comma zero. Now if it is 2008, it is supposed to give me the two which is correct to column jumping for the next match. That is for height, height is dependent on how many months is being asked. So let me count the month. Were in the selection and I press f4 Karma sido the rest of the brackets are closed appropriately.

So let me press enter 3200 Let's see if that is the case. Yeah, that is 3200 if I choose, let's say March, I'm supposed to get 4800 Yes. If I change the year let's see 2010 Yes. And alternatively, I could have also prepared a data validation list in these two cells. So data, data validation, just recapitulating from our earlier videos, I must choose a list source should be January March, all right, in the here as well, data, data validation, allow a list and in the source, let me choose the year. Okay, so anytime you choose, the answer is bound to change the one in yellow and red.

Perfect. So apply these techniques. This might seem tricky at the first at the outset, but trust me, this is a very useful formulas especially in financial modeling, were based on the inputs you would want certain values. So hope you enjoyed it.