Locked lesson.
About this lesson
Illustration and issues with the OFFSET function continued.
Exercise files
Download this lesson’s related exercise files.
OFFSET Part 2.xlsx15.4 KB OFFSET Part 2 - Solution.xlsx
15.9 KB
Quick reference
OFFSET Part 2
Discover how to use OFFSET function in a formula.
When to use
In its most basic form, OFFSET(Ref,x,y) will select a reference x rows down (-x would be x rows up) and y rows to the right (-y would be y rows to the left) of the reference Ref.
Instructions
Overview
- The syntax for OFFSET is as follows: OFFSET(Reference,Rows,Columns,[Height],[Width])
- The arguments in square brackets (Height and Width) can be omitted from the formula (they both have a default value of 1)
Example
- The above example will include Height and Width in the OFFSET formula. The SUM formula will be used to sum the cells
- OFFSET(B4,2,3,2,2) would give us an error, so it is necessary to include an aggregate function before the OFFSET function. Here the SUM function will be used. Therefore, SUM(OFFSET(B4,2,3,2,2) = 78
- This formula is summing the array of cells E6:F7
- 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.