Excel Tips - Offset Function

 

What is the Offset Function?

The Offset Function is a function in Excel that returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

How to Use Offset Function in Excel

=OFFSET(reference, rows, cols, [height], [width])

  • reference - The starting point.

  • rows - The number of rows to offset below the starting reference.

  • cols - The number of columns to offset to the right of the starting reference.

  • height – The height in the number of rows of the returned reference. This is an optional input. By default, it is the same height as the reference. 

  • width – The width in the number of columns of the returned reference. This is an optional input. By default, it is the same width as the reference. 

Offset Function in Excel

The Offset Function has inputs for five different arguments as seen above. Inputting a positive number for the rows argument results in that number of rows down, while inputting a negative number would result in that number of rows up. Similarly, inputting a positive number for the cols argument results in that number of columns right, while a negative number would result in that number of columns left.

Excel Offset Function


So what is the practicality of using this function? The Offset Function can be a very useful and dynamic tool especially when combined with other formulas and when one of the arguments can vary. Most of the time Top Shelf uses the Offset Function is when it is combined with the Sum function.

=SUM(OFFSET(reference, rows, cols, [height], [width]))

We use the above formula to calculate the forward 12-month NOI calculation or trailing 3-month NOI calculation at a specified exit date. The video above goes more into detail about this calculation.

 

 

About the Author

Brittany Martin is TSM’s Vice President who has developed real estate financial models for an extensive range of property types. She specializes in hotel and storage models. Please reach out to her if you have any questions on the Offset Function or if she can help you with your modeling needs.  

 
Brittany Martin