View More Content
SHARE

OFFSET FUNCTION IN EXCEL

OFFSET FUNCTION IN EXCEL
CategoriesTutorials

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

Use-the-OFFSET-function-to-move-down-and-right
Screenshot 1: Use the OFFSET function to move down and right
 

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.

Use-the-OFFSET-function-to-move-up-and-left
Screenshot 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.
 

Select-a-range-with-OFFSET-using-the-height-and-width-parameters
Screenshot 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).
 

Combine-all-parameters-for-full-flexibility-with-the-OFFSET-function-in-Excel
Screenshot 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:
 

Forward-looking-DSRA-implemented-with-a-flexible-OFFSET-function
Screenshot 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 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.
 

Straight-line-depreciation-coded-with-the-OFFSET-function-for-full-flexibility-in-analysis
Screenshot 6: 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 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 Training Academy - SMART 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 coureses for this topic include:

Corality Training Academy - SMART 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.

- See more at: http://corality.com/training/campus/post/march-2015/keep-your-financial-model-neat-(9-10)#sthash.otE45PbZ.dpuf

Corality Training Academy - SMART 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.

- See more at: http://corality.com/training/campus/post/march-2015/keep-your-financial-model-neat-(9-10)#sthash.otE45PbZ.dpuf

 


Rickard Wärnelid
by Rickard Wärnelid

Rickard's passion for financial modelling is built on specialist roles in the highly quantitative fields of derivatives and project finance, a career path complemented by an academic grounding in engineering physics. Born in Sweden and with global consulting and leadership experience, Rickard is an internationally recognised authority, speaker and thought-leader on the organisational benefits of best practice financial modelling.

Contact Rickard Wärnelid

view all