OFFSET function and tracing formulae

Blog

all posts

all tutorials

OFFSET function and tracing formulae

by Rickard Warnelid on March 23 2009

The OFFSET function in Excel can be a great servant but a terrible master if you do not know how to properly harness its potential. When using OFFSET it is important to keep a few things in mind.

Most people don’t understand OFFSET

I have trained hundreds of financial modelers in the art of financial modelling. Something I have discovered over the years is that it is very easy to forget what it was like to learn financial modelling for the first time when everything was new and confusing. This probably explains why so many modelers go overboard with a function once they have picked it up as they find uses for their newly discovered knowledge. The OFFSET function is a great example of this, and it is not uncommon to see someone applying OFFSET left, right and center when they have figured out how it works.

Another observation from training analysts in banks and project developers is that only a very small minority of people understand OFFSET. And of those people who say they know OFFSET, only a tiny fraction are comfortable applying OFFSET to return a range (in conjunction with say MMULT or SUM). The result is that even if you are talented enough to code up something fancy using OFFSET, most other people will not appreciate your efforts since they don’t understand it.

To make other people’s life easier - avoid OFFSET when possible.

Tracing dependents doesn’t work as expected for OFFSET

Let’s analyze a simple example of the OFFSET function.

Cell C9: =OFFSET(C3,2,3)

OFFSET example

The OFFSET formula correctly picks up the value (120) from the cell three columns to the right and two rows below cell C3. When tracing precedents of cell C9 Excel highlights cell C3 as this is the referenced cell in the formula. Using the tracing functionality on cell F5 does not give any clues whatsoever that this cell has been accessed via the OFFSET formula.

This non-traceable behavior of the OFFSET function introduces unnecessary risks in your model and should be avoided when possible.

For a more complicated application of OFFSET, have a look at the following formula.

Example of AVERAGE and OFFSET

Can you see how =AVERAGE(OFFSET(C4:E5,1,2,2,-2)) generates the result 125? I wouldn’t recommend this solution to anyone, but it is a good example of how a function can be used in a very confusing way.

Replace OFFSET with a simpler formula

One of the best properties of the OFFSET formula is that it is often replaceable by other formulae. It is often easy to replace OFFSET with LOOKUP, MATCH, SUMPRODUCT, IF, CHOOSE or SUMIF.

Send this article to a friend

COMMENTS

@John - I would say that

Submitted by Rickard Warnelid (not verified) on Tue, 05/05/2009 - 21:13.

@John - I would say that using OFFSET for picking scenarios is not a bad idea as it allows the model to grow (i.e. to add more scenarios) without facing the risk of not expanding the cell references in the formula as you have to do with INDEX and all other alternatives. My main point for not recommending INDEX is that so many people just don't understand it (basically because they have never used it) even though it is a very simple formula to understand. The worst sins are performed when combining INDEX with OFFSET to operate on ranges but that is not too common. If you just want to have the simplest scenarion chooser of them all, then why not just use a LOOKUP? Sure, it has the same drawbacks as INDEX wth the advantage that more people understand it or have seen it before. And, just for the record, I personally love INDEX, OFFSET, INDIRECT, MMULT and combinations thereof but I would never recommend using such functions in models for clients or external parties unless as an absolute last resort. I had a look at your link on the client forum - useful stuff - but just don't get me started on CHOOSE and HLOOKUP.... the kings of old school Excel modelling...

Have to say, the rule with

Submitted by John Richter (not verified) on Sun, 03/05/2009 - 05:25.

Have to say, the rule with OFFSET is only use it if you have to, not simply when it's handy. In this context, I have sympathy with David's comment re: variable life depreciation problems (or where the form of the depreciation is a set schedule that doesn't follow an algebraic derivation). In the simplest incarnation, where one only moves on a single axis (i.e. either row or column offset set to zero), the OFFSET is also tolerable. Further comments on the hated OFFSET (and why) are available at What is wrong with using the OFFSET function? Rickard, I continue to be bemused by you tolerance of what I'd consider complicated functions, but you seem to detest the INDEX (left out on your 'alternatives' list). The vast majority of OFFSET function use we see is in 'picking' scenarios, where we would say the INDEX is simple, clean, and very effective. Curious to further details on your aversion to the INDEX function? John Richter Principal Financial Mechanics http://www.fi-mech.com

@David, One can easily set

Submitted by Rickard Wärnelid (not verified) on Tue, 28/04/2009 - 10:12.

@David, One can easily set up an additional row which calculates the number of periods to 'look back', sometime referred to as a 'reverse ticker'. This additional line will then be used as an input to the OFFSET formula. Using a separate set of formulas for the initial periods is a very risky approach with many problems and my strong recommendation would be to avoid it. @Luke Sure, it is important to be careful with volatile functions and I would only use OFFSET where it is necessary. The main application is in a 'look forward' or 'look back' functionality for say a reserve account or depreciation of operational capex. Thanks for your comments, it is much appreciated.

I find that offset is very

Submitted by David Greenbaum (not verified) on Sat, 25/04/2009 - 13:49.

I find that offset is very valuable as a means to calculate depreciation with a variable useful life. One problem, I run into is that you have to swap it out with an alternate formula for the initial periods, as the offset will typically exceed the left margin of the page.

OFFSET should be used very

Submitted by Luke Wisbey (not verified) on Fri, 24/04/2009 - 17:55.

OFFSET should be used very sparingly as should INDIRECT, both are Volatile, 9 times out of 10 you should use INDEX in conjunction with a.n.other function like SUM/AVERAGE etc... I would advise anyone who thinks INDIRECT & OFFSET are great to read through Charles Williams' site, the below is a link merely from that site on Volatility in XL: http://www.decisionmodels.com/calcsecretsi.htm

Navigator Project Finance

Submitted by Rickard warnelid (not verified) on Fri, 24/04/2009 - 09:54.

Navigator Project Finance have an excellent Tutorial on how to use OFFSET efficiently. http://navigatorpf.com/training/tutorials/offset-function-in-excel

As I work in the PwC Model

Submitted by Chris McNeill (not verified) on Wed, 01/04/2009 - 20:09.

As I work in the PwC Model review team we come across this sort of thing all the time. Plus the infamous INDIRECT. We have developed a navigation tool that is able to find the 'actual' dependency, not just the cell that the OFFSET is starting from. I'd be happy to share this with anyone - I think the OFFSET is valuable for what it's worth, particularly when you are you using the height and width parameters.