Formulas discussed: Offset, Choose, Lookup, Index
The workbook used throughout this article is available here.
Both Offset and Choose are commonly used in scenario/sensitivity selections. Unfortunately, both functions have major weaknesses, even when used in something as simple as scenario selection.
The Offset syntax is
=offset(reference cell, rows to move, columns to move, height of target, width of target)
Both height and width are optional and have a default value of 1. If either of rows or columns to move are blank, they default to 0. Offset starts at the reference cell and then moves in a direction driven by the second and third inputs to select a new cell (or a range of cells if height or width are greater than 1).
The major problem with Offset is that it is hard to audit as it doesn’t reference the cells it is using, only the reference cell. This can be seen by using the “Trace Dependents” tool on the cells that we know are being picked up by the Offset function, but no arrows point in that direction. The end result is that the model is now highly susceptible to new users deleting cells that they think aren’t used in the model.
The Choose syntax is
=choose(index number, value1,value2, …..)
Index number is an integer that selects which of the parameters after it will be active. The values could be a cell reference, a formula, or a number.
The major weakness with Choose becomes apparent if you want to insert a new row. No matter where you put this row, in a scenario selection example, you would need to manually edit the Choose functions to incorporate the new data.
In most cases a simple Lookup, Index or Index/Match is capable of substituting for an Offset or Choose. Note I didn’t say Hlookup or Vlookup as I have a major bias against them. See sections 3 and 4 of the workbook for the worked examples.
What they are useful for
To be honest, I’m yet to think of any good reason to use Choose. Offset does have a purpose in life though – dynamic ranges.
Say you have built a quarterly model. You are currently negotiating with your bank for funding and one issue for discussion is whether covenants are measured on a semi-annual or annual rolling basis. One option would be to build both measures into your model. Another would be to use an Offset function so that you can choose which measure to calculate.
Try playing around with cell H69 in the workbook. The input is the number of columns you want to count in the sum function, so 2 for semi-annual and 4 for annual calculations. The issue of auditability is not as severe with this use of the Offset function, as you can see by Tracing Precedents on row 64 that it does flow through to the Offset function in row 69, so is less susceptible to accidental deletion.
Offset is also required for dynamic charts. For example, you might want to show a line graph of production volume over the life of a mine. But the mine life keeps changing, so instead of making your source data for your chart include a bunch of currently unused cells (which would then make your graph have a bunch of zero points which look ugly), you can make the chart dynamic through using an Offset function to control the size of the source data. More on that though in a later blog post.
In summary, try to avoid using Offset and Choose in your modelling due to the significant weaknesses in either their auditability, transparency or flexibility. Lookup and Index (or Sumif or Sumproduct if you want to get really fancy) are capable of completing most tasks for which you might otherwise use Offset or Choose, and are recommended in nearly all modelling applications.
Have any questions? Post them in the comments or email me at [email protected]. We’ll also take suggestions for any future topics you’d like this blog to cover.