OFFSET()
In its simplest form =OFFSET(reference, rows, cols) the offset formula returns the value in a cell a certain number of rows up/down and columns to the left/right of a starting point.
There are two additional arguments to give the full version of the formula as: =OFFSET(reference, rows, cols, [height], [width]). These additional arguments allow a range of results to be produced. Explanations of each part of the formula are as follows:
- Reference: The starting point from which you want to base the offset.
- Rows: The number of rows, up or down, that you want the upper-left cell to refer to. This can be positive (which means below the starting reference) or negative (which means above the starting reference).
- Cols: The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
- Height: The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
- Width: The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
As OFFSET() has a similar effect to INDEX() in many ways it is used far less often. However, there is one circumstance where it comes into its own and that is to make a Range Name vary as the size of the data it refers to varies i.e. it becomes dynamic (in contrast to its usual fixed form):
- When defining the name, in place of a fixed range (e.g. $D$25:$P$1234) you would enter =offset($D$25,,,counta($D$25:$D$3000),1).
- The COUNTA() sets the number of rows in the range name.
- the list must be continuous otherwise the range name will ‘lose’ some rows at the end
- It is usual to let the COUNTA() look at far more rows than you expect to need in order to ensure that it copes with all future expansions of your data
- The Cols is set to one (or a higher number reflecting the number of columns in your data). If the columns in your data also vary then you can make the range doubly dynamic by entering in the width element COUNTA($D$25:$CZ$25) instead of a fixed number.
Excel Monkey 