Excel Offset Formula

Excel Offset Formula

The Excel OFFSET formula allows you to change the cell you refer to by moving the reference either up, down, left or right as you require. This can be done automatically and is very useful for cash flow projections.

In this segment you will learn how to activate and use this function.

In this example we have Sales figures for a five month period. But what we know is that these Sales don’t get paid until either a month or two later, and what we’d like to build is when the cash is likely to be received, but to build in the option that we can change how long it takes to get the money. This can be achieved by using the OFFSET function. To activate the OFFSET function,

you click on the cell,
activate the Function Wizard
and find the OFFSET function,
and click Ok,
The first question is, what is its original reference point, and that’ll be just in the same time period so

we’ll click up here
and then you can choose if you want it to move along rows, or do you want to move along columns, in this case we want it to look along columns
And what we want it to do is to look at this column, and depending on the payment terms get the column coupled back. So if in Month 1 there were sales of 100, and there’s a one month payment period we expect that Month 2 we’ll receive the money.

· so in this case it’ll be a negative,

· and then we’ll refer to this column

· and make it absolute

You can also tell the OFFSET function to combine a number of rows, if you put a number in the height

or a number of the columns, if you put a number in the width. However for these purposes we are just going to keep it to a simple OFFSET.

if you click ok
and copy it across,
what you’ll see is at the moment it refers in the same month
but if we say that it takes one month to receive payment
if you just watch here
you’ll see then it immediately, automatically updates the fact that sales in this month will only be paid here
and we can put a 2 here
and that’ll automatically update,
so Month 2 sales of 200, are only going to be received in month 4.