The advantages of the OFFSET function in Excel…

FINANCIAL MODELLING EXPERTS

Author:

Andrew Berkley

Published:

10 Mar 2014

Comments:

0 Comments

Category:

Stuck waiting for a delayed flight, I thought I’d write a blog about the advantages of the OFFSET function in Excel.

I had a coffee.  Then I had another one.  I had an Indian head massage (that was very effective).  I then reviewed a model to see how easy it was to play with currency conversions where one currency (AED) was pegged to another (USD).  Answer: it threw up a few interesting issues.I then checked Facebook and F1F9’s cloud based communication system (we use something called Jive).

I talk about OFFSET in our training courses because it is used so frequently in non FAST models.  OFFSET is Excel’s satellite navigation system (I explain): it returns a value with reference to a fixed point.  So it shows the contents of a cell that is a number of rows and columns away from the fixed point.

My flight is being called and I still cannot think of a single advantage to using OFFSET: it is difficult to explain and review, it is not immediately understandable, it has poor transparency, dreadful audit trails and it is all too easy for it to bring up silly answers and present them as valid.

Perhaps its only merit is it helps me reach an immediate and strong opinion about the risks associated with models that do make use of it.

Andrew Berkley
Do you have a better solution than this? I’m curious to know. If you do, or if you have any thoughts on my blog, join the discussion below.If you’d like to receive more information on data visualisation, please sign up to the topic-specific mailing list.
Andrew Berkley
Andrew has spent one half of his career as a finance professional and the other half in learning and development running finance-based training courses. Andrew leads the senior team at F1F9. He has been with F1F9 since 2013.