The Excel Offset function returns range of cells that is a specified number of rows and columns from an initial supplied range.
The user can specify the size of the returned cell range.
The syntax of the function is :
|reference||-||The initial cell range that is to be offset (can be either a single cell or multiple cells)|
|rows||-||The number of rows from the start (upper left) of the initial supplied range, to the the start of the returned range|
|cols||-||The number of columns from the start (upper left) of the initial supplied range, to the the start of the returned range|
An optional argument that specifies the height of the returned rangeIf omitted, the returned range is the same height as the supplied range
An optional argument that specifies the width of the returned rangeIf omitted, the returned range is the same width as the supplied range
If the optional [height] and [width] arguments are omitted, the returned range is the same height and width as the supplied range. However, if the specified returned range goes off the edge of the spreadsheet, the function returns an error.
The following examples show the ranges that are referenced by four different calls to the Excel Offset function.
In each example, the initial supplied range is highlighted in green and the returned range is shown in red.
=OFFSET( B1:B6, 4, 2 )(note the height and width arguments are omitted and so the returned range has the same dimensions as the original range)
|=OFFSET( D3, 1, -2 )|
|=OFFSET( B2, 2, 1, 1, 4 )|
|=OFFSET( B2, 3, 0, 3, 2 )|
To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.Go to the Excel array formulas page for more details.
In each of the above examples, the Offset function returns a cell range and so the result must either be entered into a range of cells (as an Array Formula), or must be supplied to a further Excel formula that uses an array as an argument.
Both of these options are shown in example 2 below.
The following example shows a practical use of the Offset function, in which the results are used in a further Excel function.
In this example, the Offset function returns the cell range containing the earnings for the weekday 'Tuesday'. Cell B2 contains the formula to simply reference the cell range, and the formula in cell B3 calculates the total of these values, using the Excel Sum function.
In the above spreadsheet, the arguments to the Offset function in both cells, B2 and B3, are as follows:
These arguments cause the Offset function to return a reference to the range of cells B8-E8, which contains the earnings for the weekday 'Tuesday'.
As the formula in cell B2 returns a range of four cells, this must be entered into a range of four cells, as an Array Formula.
The resulting spreadsheet is shown below:
In the above spreadsheet, the formula in cell B2 is displayed in the formula bar. The curly brackets around the formula show that it has been input as an Array Formula.
The formula in cell B3 is not required to be entered as an Array Formula, as the result of the Offset function is fed directly into the Sum function.
If, in the example above, you want the offset function to retrieve the earnings for any weekday that is displayed in cell B1, you can do this by combining the Excel Match function with the Offset function. This is shown below.
In this example, the Match function identifies which row (from A6 to A12) contains the specified weekday shown in cell B1. This will give the result, 1 if B1="Sunday", 2 if B1="Monday", etc. This number then becomes the 'rows' argument for the Offset function.
Further examples of the Excel Offset function can be found on the Microsoft Office website.
If you get an error from the Excel Offset Function, this is likely to be one of the following:
|#REF!||-||Occurs if the range resulting from the requested offset would extend beyond the edge of the worksheet.|
|#VALUE!||-||Occurs if any the supplied rows, cols, [height] or [width] arguments are non-numeric.|