
Microsoft Excel Diploma Course
Microsoft Excel is an integral part of the business landscape and it is a critical...
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 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(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(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(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(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).
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.
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.
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:
Microsoft Excel is an integral part of the business landscape and it is a critical...
Excel is the world’s leading tool for data manipulation, analysis, management and reporting. The breadth...
1 day duration Explore Excel from a different dimension through Visual Basic for Applications (VBA)...
This website uses cookies.
Some of these cookies are necessary, while others help us analyse our traffic, serve advertising and deliver customised experiences for you. For more information on the cookies we use, please refer to our Privacy Policy.
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.