The OFFSET function returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell.
This function can be really useful for creating dynamic references for your formulas and charts. Here you can see a great example of OFFSET being used for a rolling chart.
The syntax for the OFFSET function is:
=OFFSET(reference, rows, columns, [height], [width])
|reference||The starting cell reference from which the offset will be applied|
|rows||The number of rows to offset from the reference. Enter a positive number for the number of rows below the reference, or a negative number for the number of rows above the reference|
|columns||The number of columns to offset from the reference. Enter a positive number for the number of columns to the right of the reference, or a negative number for the number of columns to the left of the reference|
|height||The height, in number of rows, of the returned range|
|width||The width, in number of columns, of the returned range|
Returning a Value from a Cell
Take the example data below.
To return the value of £160 from cell E2, the following formula can be used.
=OFFSET(A1, 1, 4)
This will offset 1 row down and 4 columns across from cell A1
Although the numbers are typed into the OFFSET function here. They could be returned by other functions, or form controls for more impressive examples.
Returning a Range using OFFSET
In addition to returning a value from a cell, OFFSET can also return a range of cells. This can be really useful, because it can be used to feed other functions such as SUM.
=SUM(OFFSET(A1, 1, 4, 5, 1))
This returns the result of £1720
It sums the Total column for all the products. The height and width arguments are used to select range E2:E6.
Returning the Last Value in a List
A very useful example of using the OFFSET function is to return the last value in a column, or a row. For this, we can insert the COUNTA function into OFFSET for more dynamism.
Take the example data below. This show a list of stock checks. And we want to return the value from the most recent check.
The following formula could be used.
The COUNTA function is used for the rows argument to find the bottom of the column. This moves 9 rows from B1 so a 1 is subtracted to make it the last cell in the column (instead of the first cell after the list).