OFFSET Part 2

Quick reference

• 00:04 Back to OFFSET again.
• 00:06 I'm going to be coming back twice more, actually,
• 00:10 because it's such an important function, and some very useful features.
• 00:15 And for these last two sessions, I'm going to concentrate on the fourth and
• 00:18 fifth arguments as well.
• 00:20 Namely the height and width because they do have relevance in financial modeling.
• 00:25 So enough prattle, let's get straight into Excel.
• 00:28 In this second of three sessions, I'm going to be looking at OFFSET again.
• 00:33 So far I looked at reference, rows, and columns but
• 00:36 I want to consider height and width now as well.
• 00:39 So let's put some height and width in here, shall we?
• 00:44 Don't need anything here.
• 00:53 So with height and width, let's have 2 and 2.
• 00:56 And I'm going to just put at the end here,
• 01:02 ,height, that's simple.
• 01:06 This will astound you.
• 01:09 Yep!
• 01:09 Hash value, brilliant eh?
• 01:12 So it made an error just like we do all the time with hash divs zero, hash yes,
• 01:17 hash no when we're modeling.
• 01:19 Not quite, let me explain.
• 01:21 If I actually had these four cells.
• 01:23 I went back into it in edit mode and instead of pressing Enter,
• 01:27 I held the Ctrl+Shift buttons down and then pressed Enter.
• 01:31 So I was creating what's called an array function which puts bracers,
• 01:34 that's curly brackets around the formula.
• 01:36 You can't type them in, you have to do it this way.
• 01:39 Do you see it gives me 16, 17, 22, 23?
• 01:44 Let me explain.
• 01:47 I started off here, I go down two rows, across three columns.
• 01:52 And then I have a height of two, which means this row, and the row beneath it.
• 01:57 A height of -2 will be this row, and the row above it.
• 02:00 And therefore, a height of 1 equals a height of -1.
• 02:04 It's also width of 2, which is this column and the column to the right.
• 02:09 So it's the number 16, 17, 22 and 23.
• 02:13 Do you see, it can't represent those four numbers in one side.
• 02:18 That's why it's not giving me the number.
• 02:21 It's giving me a hash value error.
• 02:24 This is easy to show while using some sort of aggregate function here which
• 02:29 summarizes them either by taking aft, the mid, the max or we'll do the sum.
• 02:34 Now, 16 + 17 is 33 + 22 is 55 + 23 is 78.
• 02:42 And that's all you need to do.
• 02:44 Now, you might be thinking, meh, or Liam, that's really changed my life.
• 02:50 I don't know how I ever managed to get this far in my career without
• 02:53 knowing that.
• 02:54 And I'd just call you sarcastic if you think like that.
• 02:57 Now, what this is is quite important, and
• 03:01 it does have an application in financial modeling, quite frankly depreciation.
• 03:06 Let's then have a look at this third example.
• 03:08 We'll do half of it in this session, and half of it in the next.
• 03:13 So, let me just explain depreciation first of all.
• 03:17 Sometimes you have to spend a lot of money in a business.
• 03:20 So for those of you who aren't accountants, what happens is you have
• 03:23 items that you buy that's then resold, but they used to generate profits.
• 03:28 It might be that you have an office building to house all your stuff.
• 03:31 It might be cars so your sales team can go around.
• 03:33 It might be plants and machinery so you can make those wonderful widgets.
• 03:37 But they tend to have a life of more than one year and
• 03:39 they're used for continuing use of the business, not for resale.
• 03:43 This is what we call capital expenditure.
• 03:45 Now the accounting rules say that when that happens,
• 03:48 you don't put it all through in one period.
• 03:49 Because that would make your profit horrendous in one period and
• 03:52 then overstated in future periods.
• 03:54 So what you do instead is you depreciate,
• 03:57 you smoothly take out the expenditure over a period of time.
• 04:02 So if I had a depreciation life, what we call economic life for
• 04:05 four years, that would mean that 100 will be spread over these four years.
• 04:10 Now there's different ways you can spread it, but we're going to go straight line,
• 04:13 which means you spread it evenly over the four periods.
• 04:17 So it will be 25 here, 25 there, 25 there, and 25 there.
• 04:21 And that's what we're going to calculate in this, and the next session.
• 04:24 That's what we're going to do, but before we can do that, we need to set this it up,
• 04:28 and I'm going to show you a practical use of the offset function.
• 04:33 Now before I do that, I need to get these numbers here down here.
• 04:37 To do that, I'm going to first of all the little formula, equals this plus one.
• 04:44 And copy it down to row 16.
• 04:47 So it appears 1 to 8.
• 04:48 Notice when I did that, I got depreciation year 1, depreciation year 2.
• 04:54 Why, because if I go Ctrl+1, Format Cells,
• 04:57 I've actually put some custom number formatting in here.
• 05:00 I've put in speech marks, depreciation and year.
• 05:04 And after the speech mark I've put the number 0, which if you remember
• 05:08 from the number formatting discussion we did earlier that means number.
• 05:12 So this is really just a number, but it looks like text that's been left-aligned.
• 05:18 And that's going to be useful for what we do.
• 05:21 Now I don't like it without the space here, so just showing you how you edit it.
• 05:26 Rather than put a space here on this sice of the speech
• 05:29 which is why I was showing you.
• 05:30 If I do it there, it will work, you see it will put the space out.
• 05:33 A lot of people do it that way, but
• 05:35 I always think it's safe to put it inside the inverted commas or
• 05:38 the speech marks because of space being the intersect operator.
• 05:41 Just to space it out, so just be careful there.
• 05:44 Now if I were to transpose, I can just highlight these and go copy.
• 05:48 And I can actually go down here and I can just go paste special,
• 05:54 Alt+E+S, and transpose, ta-da.
• 05:58 Although that shouldn't be 444, it should be 400.
• 06:02 It hasn't changed.
• 06:04 No good, so what can I do instead?
• 06:08 Well there is a transpose function in Excel.
• 06:11 I can go equals, transpose, open brackets.
• 06:14 And highlight this range here.
• 06:16 And as long as the number of cells in this range equals the number of this
• 06:21 cells in this shaded range, I press Ctrl+Shift+Enter, voila, done.
• 06:26 And if I change it back to 400 now, it works, this is an array formula, it works.
• 06:31 Cool, except if I need to insert a row,
• 06:35 only Chuck Norris can change part of an array.
• 06:40 So for reasons of flexibility, we can't do it that way.
• 06:46 So what do we do?
• 06:48 Tell you next time.

Lesson notes are only available for subscribers.