A reader asks: how does the OFFSET function work? When might I want to use it?
The OFFSET function allows the user to select a primary cell reference but return the value of a secondary cell that is “offset” from the primary cell reference by a selected number of rows and columns.
What makes OFFSET relatively tricky to other Excel functions is that not all of the components “light up” when you view the guts of the formula (by hitting F2 or double-clicking on a cell), or that sometimes people will refer to the current cell in the function logic.
Check out the explanation and applications in the Excel file below, and post your questions! (Incidentally, to get the cells to light up, you’ll need to download the embedded file below by clicking on the Excel icon in the bottom of the embed border.)
Some further notes for Application #2:
In the Basic example, the formula says the following:
Pull the value from the cell that is one row up from the current row, and two columns to the left (back in time) from the current column.
In the Advanced example, the formula says the following:
Take the cumulative sum of all unit renovations started since the first period, up through the number of renovations that are in the cell that is one row up from the current row, and two columns to the left (back in time) from the current column.
