The advantages of the OFFSET function in Excel…



Andrew Berkley


10 Mar 2014




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 instant messaging system – we use Slack.

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
With a background in business education and financial advisory work, Andrew leads financial modelling training at F1F9. He has been with F1F9 since 2013.