The Onset of OFFSET

This article considers how to use the OFFSET function effectively in a financial model. By Liam Bastick, director with SumProduct Pty Ltd.

Query

I want to undertake some scenario analysis in my financial model, whereby certain key assumptions can be changed. I need to keep the alternative scenarios and I may need to add to them. I am aware of Excel’s Scenario Manager, but I’m looking for something more transparent. Any suggestions?

Advice

In another article, I have considered INDEX and MATCH, functions concerned with position; this month we look at OFFSET, a function which considers displacement. OFFSET is a function well suited to this scenario.

The syntax for OFFSET is as follows:

The arguments in square brackets (Height and Width) can be omitted from the formula (they both have a default value of 1 which is explained further below).

In its most basic form, OFFSET(Ref,x,y) will select a reference x rows down (-x would be x rows up) and y columns to the right (-y would be y columns to the left) of the reference Ref. 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:

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

We can use these mechanics to answer this month’s query and construct a very simple scenario table:

This example is included in the attached Excel file. Essentially, the assumptions used in the model are linked from cells J14:J20 (in yellow). These values are drawn from the scenario table to the right of the highlighted yellow range (e.g. cells L14:L20 constitute Scenario 1, cells M14:M20 constitute Scenario 2).

The Scenario Selector is located in cell J11. Using OFFSET we can retain all scenarios and select as we see fit. For example, the formula in cell J14 is simply OFFSET(K14,,$J$11), that is, start at cell K14 and displace zero rows and the value in J11 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 is that the range of data can be added to. Whilst the other functions require a specified range whereas we can keep adding scenarios without changing the formula / making the model inefficient.

Other Uses

OFFSET can be used for other practical uses in Excel, taking advantage of the Height and Width arguments.

Consider our OFFSET example from earlier. If we extend the formula to OFFSET(D4,-1,-2,-2,3), it would again take us to cell B3 but then we would select a range based on the Height and Width parameters. The Height would be two rows going up the sheet, with row 3 as the base (i.e. rows 2 and 3), and the Width would be three columns going from left to right, with column B as the base (i.e. columns B,C and D).

Hence OFFSET(D4,-1,-2,-2,3) would select the range B2:D3, viz.

Note that OFFSET(D4,-1,-2,-2,3) = #VALUE! since Excel cannot display a matrix in one cell, but it does recognise it. However, if after typing in OFFSET(D4,-1,-2,-2,3) we press CTRL + SHIFT + ENTER, we turn the formula into an array formula: {OFFSET(D4,-1,-2,-2,3)} (do not type the braces in, they will appear automatically as part of the Excel syntax). This gives a value of 8, which is the value in the top left hand corner of the matrix, but Excel is storing more than that. This can be seen as follows:

• SUM(OFFSET(D4,-1,-2,-2,3)) = 72 (i.e. SUM(B2:D3))
• AVERAGE(OFFSET(D4,-1,-2,-2,3)) = 12 (i.e. AVERAGE(B2:D3)).

Indeed, we can construct a simple depreciation calculation, transpose references or even build a dynamic chart (one that displays more / less categories of information as required) using OFFSET’s Height and Width functionalities. The associated Excel workbook provides several examples as illustrations.

Handle With Care

While OFFSET is an extremely useful and flexible function, it is not transparent. The syntax is not only difficult for the user to grasp, Excel’s auditing tools have problems too. The Reference cell in the OFFSET arguments is the sole component that will be identified, e.g. OFFSET(A1,2,3) will trace cell A1 only.

Furthermore, as stated above, spreadsheet users do get confused – in particular, regarding the Reference. When calculating the Rows and Columns components, the Reference cell is excluded, but it is included in determining the Height and Width.

If you have a query for this section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the website www.sumproduct.com