Offset function in Excel

The OFFSET( ) function returns a cell (or range of cells) that is a specified number of rows and/or columns from the reference cell. In this tutorial we will explain the most common OFFSET( ) applications, and mistakes that are often made using this function in Microsoft Excel.

The syntax for OFFSET( ) is OFFSET (cell reference, rows, columns, [ height ], [ width ]). Components in square brackets can be omitted from the formula.

How does the Excel function OFFSET( ) work?

The OFFSET( ) function returns a cell (or range of cells) that is a specified number of rows and/or columns from the reference cell. For specific descriptions of each component, please see the Help file in Excel.

If either the ‘rows’, ‘columns’, ‘height’ or ‘width’ components are left blank, Excel will assume its value to be zero. For example, if the formula is written as OFFSET(C38, , 1, , ), Excel will interpret this as OFFSET(C38, 0, 1, 0, 0). This can also be written as OFFSET(C38, , 1) since ‘height’ and ‘width’ can be omitted.

Note: If ‘height’ and ‘width’ are included in the formula, they cannot be equal to zero or a #REF! error will result.

Four examples illustrate the function below, showing the impact of the different parameters in the OFFSET function.

OFFSET example 1 – basic mechanics

OFFSET(D10, 1, 2) will give the value in F11 or ‘7’, i.e., Excel returns the value in the cell one (1) row below and two (2) columns to the right of D10.

OFFSET example 2 – moving up and/or left with negative parameters

OFFSET(G12, -2, -2) will give the value in E10 or ‘2’, i.e., Excel returns the value in the cell two (2) rows above and two (2) columns to the left of G12.

OFFSET example 3 – selecting an area using height/width parameters

OFFSET(F12, , , -2, -3) will return the two (2) row by three (3) column range D11:F12. Note that the reference cell F12 is included in this range.

OFFSET example 4 – combining all parameters

OFFSET(D10, 1, 1, 2, 3) will return the range E11:G12, i.e., Excel first calculates OFFSET(D10, 1, 1) which is E11 (one (1) row below and one (1) column to the right of reference cell D10), then applies the formula OFFSET(E11, , , 2, 3).

Common problems and mistakes with the OFFSET function

• When tracing OFFSET( ) functions, only the reference cell is returned; for example, when tracing the precedent of OFFSET(D10, 1, 1, 2, 3) the returned cell is D10 and not E11:G12
• Excel excludes the reference cell when calculating the ‘rows’ and ‘columns’ components, but includes the reference cell when calculating the ‘height’ and ‘width’ components – this can be confusing, and requires extreme care
• OFFSET( ) is a complex concept to grasp which reduces user confidence in the model since it is not easily understood

Combining OFFSET( ) with other functions

• Since OFFSET( ) returns a cell (or a range of cells), it can be easily combined with other functions such as SUM( ), MIN( ), MAX( ), AVERAGE( ), etc.
• For example, SUM(OFFSET( )) calculates the sum of the cell (or range of cells) returned by the OFFSET( ) function.
• Following example 4 above, SUM(OFFSET (D10, 1, 1, 2, 3)) is equivalent to writing SUM(E11 : G12) (as OFFSET (D10, 1, 1, 2, 3) returns the range E11 : G12) which equals 54 = 6 + 7 + 8 + 10 + 11 + 12. Similarly, AVERAGE (OFFSET (D10, 1, 1, 2, 3)) is equivalent to AVERAGE (E11 : G12).

Common uses for OFFSET( )

Forward-looking debt service reserve account (DSRA)

DSRA target balance is usually calculated as the sum of future expected debt service. As such, OFFSET( ) is used when calculating a dynamic DSRA target balance. An example is shown below:

In the above screenshot, the target DSRA balance is the sum of the next two quarters’ debt service (as specified in H24). Since each column represents one quarter, the target DSRA balance can be calculated by adding the debt service of the next two (2) columns. Resulting in the equation SUM(OFFSET (I23, 0, 0, 1, $H24)). This is equivalent to SUM(OFFSET (I23, 0, 0, 1, 2)) or SUM(I23 : H23).

The user can then change the Lookforward Period in H24, without altering the formula.

Straight line depreciation using OFFSET

For instances where maintenance during operation is capitalised, then depreciated using the straight line method, only those additions within the depreciable life span should be included.

• Here the depreciable period is 20 quarters, or five (5) years
• The percentage to be depreciated per quarter is 5% = 1/20
• Lookback Periods (row 37) is the minimum 20 quarters, or the number of additions (row 40) prior to the current period
• Since ‘height’ and ‘width’ components are used in the formula OFFSET(I40, 0 , 0, 1, -J37), the IF ( ) statement is required to eliminate any errors when the Lookback Period is zero
• SUM (OFFSET (I40, 0, 0, 1, -J37)) is equivalent to SUM (OFFSET (I40, 0, 0, 1, -2)) or SUM (H40 : I40), which is the sum of all additions two (2) quarters’ prior to quarter ending June 2015
• The total is multiplied by D42 to calculate the depreciation amount

Corality Academy – Corality Financial Modelling Campus

Visit our website to take advantage of various training courses and free resources to assist you in reducing spreadsheet risk and efficiently building your models with confidence.

Some of our related training courses for this topic include:

• Excel Diploma Series: Excel Diploma – Reach your Excel potential, Business Analytics in Excel, and Mastering Excel with VBA
• Best Practice Project Finance Modelling