OFFSET

OFFSET(reference, rows, columns, [height], [width])

The arguments in square brackets (height and width) may be omitted. In its most basic form, OFFSET(reference, rows, columns) will select a reference rows rows down (-rows would be rows rows up) and columns columns to the right (-columns would be columns columns to the left) of the reference. For example, consider the following grid,

OFFSET(A1,2,3) would take us two rows down and three columns across to cell D3. Therefore, OFFSET(A1,2,3) = 16, viz.

OFFSET(D4,-1,-2) would take us one row up and two rows to the left to cell B3. Therefore, OFFSET(D4,-1,-2) = 14, viz.

It is this OFFSET displacement technique (described above) that can create a scenario table:

Essentially, the assumptions used in the model are linked from cells L17:L24. These values are drawn from the scenario table to the right of the highlighted yellow range (e.g. cells N17:N24 constitute Scenario 1. The “Base” case, cells O17:O24 constitute Scenario 2).

The Scenario Number is located in cell H12. Using OFFSET, we can retain all scenarios and select as we see fit. For example, the formula in cell L18 (highlighted) is simply =OFFSET(M18,,$H$12), that is, start at cell M18 and displace zero rows and the value in H12 columns across. In the illustration above, the formula locates the cell one column to the right, which is Scenario 1.

The advantage of OFFSET over other functions such as INDEX, CHOOSE and LOOKUP functions (check back on my previous articles) is that the range of data can be added to without having to amend their respective formulae.  The image below shows Scenarios 6 and 7 added in an instant.  Whilst the other functions require a specified range whereas we can keep adding scenarios without changing the formula / making the model inefficient.

It should be noted that OFFSET is a volatile function, i.e. a function that causes recalculation of a formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether precedent cells / calculations have changed, or whether the formula also contains non-volatile functions. One test to check whether your workbook is volatile is close a file after saving and see if Excel prompts you to save it a second time (this is an indicative test only).

OFFSET is also what is known as a non-auditable function in that it does not recognise dependent cells that are linked via an OFFSET function. For example, in my illustration above, the $3.70 in cell N18 is clearly used. However, if you were to select this cell and trace dependents (ALT + M + D), you would get the following message:

This should not put you off using OFFSET; it is a function that frequently calculates much quicker than the alternative options and its advantages may often outweigh the potential pitfalls.

Word to the Wise

As stated above, a volatile function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether precedent cells / calculations have changed, or whether the formula also contains non-volatile functions. One test to check whether your workbook is volatile is close a file after saving and see if Excel prompts you to save it a second time (this is an indicative test only). This can really slow down your model if there are too many OFFSET functions, for example.

Just because a function is volatile in one version of Excel does not mean it is volatile in all versions. Perhaps the best example of this is INDEX, which was volatile prior to Excel 97. Microsoft still states this function is volatile, but this does not appear to be the case except when used as the second part of a range reference, for example $A$1:INDEX($A$2:A$10,4), will also cause the reference to be flagged as “dirty” (i.e. needs to be recalculated) when the workbook is opened only.

VLOOKUP may be considered volatile too; if you change data in the table referenced by the function which is in neither the lookup column nor the results column, VLOOKUP will still recalculate.  Therefore, I present yet another reason to exclude VLOOKUP from future family function (get it?).

Another common ‘semi-volatile’ function is SUMIF, which has been so since Excel 2002. This function becomes volatile whenever the size of the first range argument is not the same as the second (sum_range) argument, e.g. SUMIF(A1:A4,1,B1) is volatile whereas SUMIF(A1:A4,1,B1:B4) is not.

IF and CHOOSE (which I shall talk about next month) do not calculate all arguments, but if any of the arguments are volatile – regardless of whether they are used – the formula is deemed to be volatile. Therefore, IF(1>0,1,RAND()) is always volatile, even though the value_if_false argument will never be calculated. It is not quite as simple as this though. If the formula in cell A1 is =NOW() then this cell will be volatile, but IF(1>0,1,A1) will not be.

In essence, direct references or dependents of volatile functions will always be recalculated, whereas indirect ones will only recalculate when activated or in certain other functions that always calculate all arguments such as AND and OR.