OFFSET Formula – an Easy Example

The background

One of my friends has recently asked me which Excel formula do I consider to be the most powerful. There are many aspects to define what is powerful. If I define it like “Which formula has saved me the most of time after I mastered it?” – then the answer obviously is the OFFSET formula.

I have to admit that I’ve mastered the formula late enough – somewhere at the end of 2016. This formula was the key to save some time at one of the ModelOff Round 2 cases that dealt with depreciation. I still qualified for the finals that year, but that was due to success at solving the other cases.

I will use an example of a CAPEX and depreciation projection model to illustrate how the OFFSET formula works. You can download the file on my CFOTemplates.com website here.

The idea in this case is simple: there is a company that is planning to invest some money into a new factory, new manufacturing equipment, new trucks and some smaller items. Having a 5-year monthly scope of the model, I don’t want to forecast depreciation of telecom equipment (3-year straight-line depreciation assumed) for longer than necessary.

OFFSET Formula – the Explanation

There is one simple idea that would make the idea behind the OFFSET formula crystal clear for you. The OFFSET formula essentially defines a rectangle of cells for you (usually this rectangle is called an array). This rectangle has a precise address (e.g. A1:B2) and you can use other formulas to define how the size of the rectangle should be changing. Then you can put any other formula around this range, e.g. SUM or AVERAGE, to get a meaningful result.

For example, I need to calculate depreciation for the telecom equipment on month 38 since the beginning of the projections. Telecom equipment is being depreciated in this case on a 36-month basis. To do the precise calculations, I need to sum the CAPEX for months 2 to 37 and then divide the sum by 36. However, using a formula like “=sum(E99:AN99)/36” won’t help in case the assumptions change and the telecom equipment now gets depreciated within e.g. 60 months. Essentially, the size of the rectangle will change.

Instead, I use the OFFSET function. I know that the rectangle should definitely include the previous month, so I start the formula with “=OFFSET(AN99…”. The next 2 properties of the formula define whether the range should go up or down from the cell AN99. We don’t really need any movements here, so let’s put two consecutive zeros: “=OFFSET(AN99,0,0,…”

Now is the trickiest part – we need to understand the height and the width of the rectangle. The height is just 1 row, so I continue the formula with “=OFFSET(AN99,0,0,1…”. The width is tricky – it should be 36 months, but in case we copy the formula to earlier months, the width of the rectangle should still be within the boundaries of the CAPEX table. So, it is the minimum of either the depreciation period in months, or the number of the previous month: MIN(AN93,C110).

Putting this all together gets us with a formula like “=OFFSET(AN99,0,0,1,MIN($C110,AN$93)”. The outcome of such formula would be a #VALUE error, but as soon as you wrap it with the SUM function, the magic will happen and you will get the total CAPEX amount for the last 3 years: “=SUM(OFFSET(AN99,0,0,1,MIN($C110,AN$93))”. Then just divide by C110 (the number of months) and wrap the resulting formula with IFERROR function to avoid errors if you don’t have the length of the planning period defined in C110.

So, to sum up: OFFSET returns a rectangle of cells that you can move around and resize with the help of other formulas. You can do whatever you want with this array – sum, calculate an average value, etc. Now try to play around with different parameters of the formula – practice makes perfect!

See also the official help page on the OFFSET formula by Microsoft here.

P.S. You are welcome to connect or follow me on LinkedIn to get new ideas about financial modeling. I will be posting useful tips and tricks, as well as tutorials in the field of financial modeling.