OFFSET Part 3

Quick reference

• 00:04 Third time lucky with OFFSET.
• 00:06 It's an important function, which is why we're devoting a bit of time to it,
• 00:09 because it is useful in financial modeling.
• 00:12 Again, like last time, I'm concentrating on height and width in the syntax.
• 00:19 So let's go straight back to the example I was looking at last time.
• 00:24 Since last time, I've put a little bit of formatting on just to make it look
• 00:28 a little bit prettier, just to sort of speed things along so
• 00:32 we can concentrate on the good stuff.
• 00:34 We had a problem with transpose.
• 00:36 We wanted to actually transpose these numbers, and put them in here.
• 00:40 We tried with copy paste special transpose, and
• 00:43 we also tried with the transpose array function, and both demonstrated problems.
• 00:48 That is a simple solution.
• 00:50 Yes, it's our old friend, which we've been going on and on about,
• 00:52 the offset function.
• 00:54 Simply =OFFSET( and anchor this cell,
• 00:58 the first cell to the left of the stuff you want to transpose.
• 01:02 Now there gonna rows down so I can put note, but that's making it busier than
• 01:07 it needs to be, so I'll just put ,, a big club fan there.
• 01:12 And then what I'm going to do Is actually move it across so
• 01:16 many columns based on this counter.
• 01:18 Remember, that's just a number that looks like text.
• 01:21 That's one column across.
• 01:23 Going down, two columns across, three columns across, easy transpose.
• 01:29 Now, in the next course where we build the financial model
• 01:33 I actually talk about depreciation in detail.
• 01:37 A bit of a summary on this one here though is I'm going to just show you a quick and
• 01:42 dirty version of it, using the main function, minimum.
• 01:46 So we're gonna talk about min in a few minutes anyway, but
• 01:49 it's a pretty easy function.
• 01:51 Min just takes the minimum of two or more values, could be cell references,
• 01:55 could be values, could be mixture.
• 01:57 And so, what I'm going to do is here, is I'm gonna put equals the minimum of what
• 02:01 would be the depreciation charge, which is this number here.
• 02:04 Dollar C-9 pressing the F4 function key and the economic life,
• 02:10 which I'll make absolute by pressing the F4 function key just one, $D$4.
• 02:16 And what's left is going to be well the original balance,
• 02:20 $C9- the sum of what's been depreciated to date.
• 02:23 And this is why I need this grayed out blank column,
• 02:27 because I need a blank column there.
• 02:29 I click on that and I press the full stop little trick.
• 02:32 It puts up D9:D9 and I put a $ just in front of the first D of the first D9.
• 02:40 ) ) again.
• 02:43 Copy that across.
• 02:44 So that's taking the minimum therefore of the depreciation charge and what's left?
• 02:51 So if I put this to 4.5, it won't put 22 in here because it's already depreciated.
• 02:58 This much, it's already depreciated 88.8.
• 03:01 There's only 11.1 and a bit to go.
• 03:04 That's what's going on.
• 03:05 So you'll see here, that's it subtracting off that.
• 03:08 This one subtracts off that.
• 03:10 That one subtracts off all of this, etc.
• 03:13 I can copy this down by knocking out these first ones.
• 03:21 I'll change this back to four at the same time.
• 03:30 Waiting for the computer to catch up there.
• 03:34 This is my total depreciation charge, so for instance,
• 03:37 this represents four different depreciation charges going through.
• 03:40 It's the last year of the year one depreciation and
• 03:43 pertinent year of the year two, second year of the third year and
• 03:49 the first year, or the fourth, all added up.
• 03:52 Now, it's nice and transparent, but if I had a 20-year model and
• 03:55 I was modeling monthly, I'd need 240 columns.
• 03:58 I'd also need 240 rows.
• 04:01 Now, 240 times 240 is 57,600.
• 04:05 You saw how quickly that was calculating.
• 04:07 What's it gonna do with 57,600 calculations?
• 04:11 I'm gonna get bored trying to read the model.
• 04:14 Sometimes you have to balance transparency with robustness.
• 04:18 And here's a little trick then.
• 04:20 So what I'm gonna do is I'm gonna put in an adjusted counter for
• 04:23 an alternative method, which doesn't require as many calculations.
• 04:27 The counter is going to be equal to the minimum of this counter here.
• 04:33 And the economic life which I make absolute.
• 04:38 So this is going to go 1, 2, 3, 4, 4, 4, 4, 4, 4.
• 04:42 Or, if I change to five 1, 2, 3, 4, 5, 5, 5, 5, etc.
• 04:46 I'm just restricting it.
• 04:50 I then put through my depreciation charge and
• 04:55 that's just simply going to be equal to this divided by the economic life.
• 05:06 Then to calculate my actual total, my total depreciation,
• 05:11 is simply going to equal.
• 05:14 Simply I say,
• 05:34 Two things will have happened here.
• 05:36 One, you'll see that these numbers equal those.
• 05:40 And secondly, you realize you don't understand this.
• 05:43 It's not that hard.
• 05:45 It's actually saying okay starting this cell E20 and
• 05:49 then don't move any rows across.
• 05:51 We'll not displace it, we'll just take advantage of the height and width.
• 05:55 I've not specified the height and default is one.
• 05:57 So when the height and width are not specified, the default is 1 for both.
• 06:01 But the width I specified is minus E19, is this value.
• 06:05 So it's a width of -1.
• 06:06 So it's going up to here.
• 06:07 A width of minus 1 is just this 25, and it'ssum in it.
• 06:11 In this period, it's looking at F20, the 50.
• 06:14 It's not moving any rows or columns.
• 06:17 It's up to a height of 1, so it's just in this row, but a width of minus 2.
• 06:21 So it's that 50 plus that 25.
• 06:23 Just the same as that, which is 75.
• 06:26 This one here is going up to G20.
• 06:29 We're not moving any rows of columns.
• 06:31 We've got a height of ones, so we're on row 20, and a width of minus3.
• 06:34 So it's that, plus that, plus that, which is 150, which is those.
• 06:39 Do you see how it works?
• 06:41 It's maybe not quite as transparent to start off with.
• 06:45 But it makes the model a lot smaller and therefore easy to work with.
• 06:49 And that's why OFFSET's important.

Lesson notes are only available for subscribers.