View More Content
SHARE

OFFSET FUNCTION IN EXCEL

OFFSET FUNCTION IN EXCEL
Tagsexcel

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?

OFFSET example 1 - basic mechanics

Image 1: Use the OFFSET function to move down and right
 

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 fucntion

OFSET(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.

Image 2: Use the OFFSET function to move up and left

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.

Image 3: Select a range with OFFSET using the Height and Width parameters

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).

Image 4: Combine all parameters for full flexibility with the OFFSET function in Excel

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:

Use OFFSET with a DSRA in financial modelling
Image 5: Forward-looking DSRA implemented with a flexible OFFSET function

In the above screenshot, the target DSRA balance is the sum of the next two quarters’ debt service (as specified in E39). 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 (M38, 0, 0, 1, $E39)). This is equivalent to SUM (OFFSET (M38, 0, 0, 1, 2)) or SUM (M38 : N38).

The user can then change the Lookforward Period in E39, 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.

OFFSET for depreciation calculation in financial modelling
Screenshot 3: Straight line depreciation coded with the OFFSET function for full flexibility in analysis
  • 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 47) is the minimum 20 quarters, or the number of Additions (row 50) prior to the current period
  • Since ‘height’ and ‘width’ components are used in the formula OFFSET(N50, 0 , 0, 1, -O47), the IF ( ) statement is required to eliminate any errors when the Lookback Period is zero
  • SUM (OFFSET (N50, 0, 0, 1, -O47)) is equivalent to SUM (OFFSET (N50, 0, 0, 1, -3)) or SUM (N50 : L50), which is the sum of all Additions three (3) quarters’ prior to quarter ending June 2009
  • The total is multiplied by F51 to calculate the depreciation amount

Rickard Wärnelid
by Rickard Wärnelid

Contact Rickard Wärnelid

view all