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 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 of 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 that if “height” and “width” are included in the formula, they cannot be equal zero or a #REF! error will result.
Examples below illustrate the function. Given the following set of numbers

Screenshot: Initial number series for OFFSET examples

OFFSET Example 1

OFSET(D10, 1, 2) will give the value in F11 or 7, ie, Excel returns the value in the cell 1 row below and 2 columns to the right of D10 

OFFSET Example 2

OFFSET(G12, -2, -2) will give the value in E10 or 2, ie, Excel returns the value in the cell 2 rows above and 2 columns to the left of G12

OFFSET Example 3

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

OFFSET Example 4

OFFSET(D10, 1, 1, 2, 3) will return the range E11:G12, ie, Excel first calculates OFFSET(D10, 1, 1) which is E11 (1 row below and 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. Extending from 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 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 targe balance. Example is shown below:

Screenshot #1: Forward-looking DSRA

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 2 columns, hence 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.

Screenshot #2:Straight-line Depreciation 

• Here the depreciable period is 20 quarters, or 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 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.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...