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.
The OFFSET function provides powerful flexibilty in some particular situations, and this tutorial is inspired by may other Excel training course examples in the Excel Diploma course.
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.
Working professionally with Excel can be greatly improved with the used of Keyboard shortcuts for Excel.
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:
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.
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