16. OFFSET: Startup and investor excel model training

OFFSET is a weird function. I only like using it when I absolutely have to. Some people use it a lot, but it’s impossible to audit properly since you can’t ‘see’ what the formulas link to. It’s a bit like magnetism, you know it works but you can’t see it!

Intro to Excel model training

This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.

This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.

To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.

You can join up to the training here.

What’s the point of the OFFSET?

There are instances when you just have to use offset. What you are doing is so complicated, it needs to happen. There are other times that you think ‘yay, I know a complicated way to do something simple, I’m going to do that.” Don’t be that dude.

Here is a real example from the 50Folds SaaS model. The way revenue is forecasted by cohorts is hardcore. You’d think it wouldn’t be that hard to calculate annual payments with a discount and you’d be reeallllyy wrong.

This fricking formula took me days to figure out… especially since I accidentally pasted over the formulas and I had to figure it out twice… FML.

In terms of using OFFSET when you don’t really need to… we’ll actually learn that in today’s class.

Now there is some good reason to use this method actually. It can really stop you from messing up SUM() calculations for totals when you keep adding in and removing rows, but I don’t like things I can’t see.

I can’t audit with OFFSET() so I’m an OFFSET racist.

There I said it. Don’t judge me.

So what’s the story with the OFFSET thingamybob. Think about a being a sniper. You get to pick where it’s going to go down and where the bullet is going to hit. Only the hideout is called the reference and the dead person is determined by moving up/down the columns/rows. So you order the hit at A1 and Sadam is at B2 which is down one row and across one column…

The syntax is this:

=OFFSET(

reference,

rows,

cols,

[height], [width]) – > ignore this stuff

reference – This is the starting point “cell reference”
rows – The number of rows to move from the reference.
cols – The number of columns to move…
height – [LALALALALALA] The height in rows of the returned reference. I have no idea what this stuff does… ignore it
width – [LALALALALALA] The width in columns of the returned reference. I have no idea what this stuff does… ignore it

How do I do these things you speak of?

This is a pointless example, but say you are a sniper and you want to **** up a hipster. No one would blame you.

So you are in A1. The hipster is in C3. So that’s across 2 and down 2, right?

So easy. Write: =OFFSET(A1,2,2,)

I’m actually going to teach you a pragmatic use of offset in the example, so just do that instead. No, I’m not going to teach you crazy ass applications of OFFSET as that will take way to long.

I love you. Just not that much.

Example

The excel example sheet will help you to learn this with an example.

To get the training model, subscribe to the series below.

16. OFFSET: Startup and investor excel model training

Intro to Excel model training

What’s the point of the OFFSET?

How do I do these things you speak of?

Example

Get in the game

Comments (0)