OFFSET

Quick reference

• 00:05 So far, I've been talking about position a lot.
• 00:09 Functions that return the position of something.
• 00:11 Functions that find the corresponding position of something.
• 00:15 Functions that actually give you the value of something in a particular position.
• 00:19 I've been talking about INDEX, MATCH, and LOOKUP.
• 00:23 I've not been talking about VLOOKUP and HLOOKUP,
• 00:26 which I'm trying to deter you from using.
• 00:29 Position, position, position, very important in financial modeling.
• 00:33 But now I want to move on.
• 00:33 I want to talk about disposition, rather than that position.
• 00:38 Sorry, the jokes aren't getting any better.
• 00:41 With this born in mind, I want to talk about the OFFSET function.
• 00:46 The OFFSET function returns a reference to a range that is a specified number of rows
• 00:51 and columns from a cell or range of cells.
• 00:53 Typically, we just use a cell.
• 00:55 The references returned can be a single cell or a range of cells.
• 00:59 And usually, it's a single cell, too.
• 01:00 You can specify the number of rows and the number of columns to be returned.
• 01:05 The syntax has up to five arguments, but is typically only using three.
• 01:11 It's =OFFSET, a particular reference, the number of rows, number of columns,
• 01:17 height and width in square brackets, not because you put them in square brackets,
• 01:21 but because of the fact they're optional.
• 01:23 So the reference is the reference that you want to use as the base.
• 01:26 Rows is the number of rows, up or down,
• 01:28 that you want the upper-left cell to refer to.
• 01:30 So by this, if it's going down the page, it will be a positive number.
• 01:35 If it's going up the page, it's going to be negative.
• 01:38 It's the same thing reading like a book left to right down the page.
• 01:43 cols, the columns, works similarly, left to right.
• 01:46 So if you're in this column and columns to the right, it will be a positive number.
• 01:49 If you go from this column to a column to the left, it will be a negative number.
• 01:54 height's the height in number of rows, if it's down, positive, if it's up, negative.
• 02:00 And similarly,
• 02:01 width is the width in the number of columns that you want to be returned.
• 02:04 And if it's to the right, it's going to be positive.
• 02:07 And if it's to the left, it's going to be negative.
• 02:10 Now OFFSET can be used for a myriad of things in financial modeling.
• 02:14 And one of them is what we call sensitivity analysis,
• 02:17 when you look at scenarios.
• 02:19 Which is a bit confusing cuz sensitivity analysis is both a specific thing and
• 02:24 a generic thing.
• 02:26 And I'm going to give you an example here so that I'm going to stop confusing you.
• 02:30 Let's go and look at Excel.
• 02:33 I'm going to spend two sessions looking at the OFFSET function
• 02:37 because it's really quite a useful function in Excel if used correctly.
• 02:41 It's quite important, it can be used in a basic manner or more complex.
• 02:45 I'm going to start off though with basic illustrations after I've compared and
• 02:50 contrasted with a function it often gets confused with, the INDEX function.
• 02:55 So I'm going to start off with an illustration that I use to actually
• 02:59 demonstrate INDEX in its array form.
• 03:01 An array being more than one row and more than one column.
• 03:04 You'll recall, I use this table to show the INDEX function before.
• 03:09 So I'm just going to recap, =index, you highlight the whole array.
• 03:15 Cells B4 to G9, then the Row Number will be row 2 in cell D12.
• 03:21 And the column number will be in D13, 3.
• 03:25 So we're going to go 2nd row, 3rd column, 9.
• 03:28 2nd row, 3rd column, the intersect is here,
• 03:33 9, that's how INDEX works.
• 03:36 It returns the position of the intersection of a row number and
• 03:40 a column number.
• 03:41 That's not how OFFSET works, OFFSET works like this.
• 03:45 Let's just look at the three arguments here.
• 03:48 It requires a base cell, not the whole range, so I'm just going to have cell B4.
• 03:52 I've only been here before.
• 03:55 Anyway, then I'm gonna go so many rows down and so many columns to the right.
• 04:02 That's not going to give me the value 9.
• 04:03 It's going to give me the value 16 because we're gonna start here.
• 04:07 I'm gonna go 2 rows down, 1, 2, and 3 across, 1, 2, 3, 16, that's how it works.
• 04:15 If it's negative, it will go up the page or to the left instead.
• 04:19 So let me try a different one here, I think I can get rid of this now.
• 04:24 Let's instead have 2 and minus 3, and I'll start in this cell here, 11.
• 04:31 The REF comes up at the moment because it's going off the page.
• 04:34 Cell B4 is the base, I'm gonna go down two rows.
• 04:40 And then I'm going to go across three columns left.
• 04:43 Can't, one, and I'm off the sheet.
• 04:46 There's no columns, there's no minus A and minus B.
• 04:49 That's why you get a REF error, so I'll start with something else.
• 04:52 I'll go, =offset(, I'll start here in cell F5,
• 04:57 and then go there and there, 20.
• 05:01 Because it goes 2 rows down, 1, 2, but
• 05:05 then it goes 3 columns to the left, 1, 2, 3.
• 05:10 And that's it, nice and simple, make sense?
• 05:16 So practicality, it's often used for scenarios.
• 05:22 Let's take this where I've got scenarios 1, 2, 3, 4, and 5 here.
• 05:26 Lots of different inputs.
• 05:27 So if I chose scenario 1, I get these numbers being used in the model.
• 05:32 So if I go Formulas, Trace Dependents, you can see these are being used in my model
• 05:37 down here to calculate a nice little PNL down here.
• 05:46 Nice and simple.
• 05:47 And this formula is going OFFSET, start in cell G9, which is here.
• 05:53 And then go F6, one column across, notice it goes comma comma.
• 05:59 Well, that's the same as doing comma 0 comma.
• 06:02 It means don't go any rows down, just go one column across.
• 06:06 Start here, go one column across.
• 06:07 If I go there, it goes two columns across, or three columns across, or
• 06:11 four columns across, or five columns across, depending on what I choose.
• 06:17 Now the reason I prefer OFFSET to things like LOOKUP or
• 06:21 INDEX here is sometimes you want to actually have new ones coming in.
• 06:26 Look, I can just put scenario 7 in and it works, that's the beauty of this.
• 06:32 These sort of things often happen at the last minute in a model.
• 06:35 And take time up, and you don't have time to change it.
• 06:38 And these inputs still feed through into this model down here.
• 06:41 So all our inputs come from these numbers here.
• 06:44 And these feed in on the OFFSET function from here.
• 06:47 So even at the last minute, you can add other scenarios, should you wish.
• 06:51 And a scenario is just when you change a few inputs at the same time, best case,
• 06:55 base case, worst case, whatever.
• 06:57 Very practical in the world of financial modeling.
• 07:00 Now more on OFFSET next time.

Lesson notes are only available for subscribers.