Introduction to Offset Function
It is well-known that OFFSET() function in MS Excel returns a cell that is a particular number of rows and columns from a cell or range of cells. It's handy in formulas that dynamically average or the sum of "last nth values".
In this small article, I'll explain the syntax and examples of the OFFSET function in Microsoft Excel.
Syntax of OFFSET Function
=OFFSET(reference, rows, cols, [height], [width])
- reference represents the kick off point, supplied as a cell reference or range.
- rows are the number of rows to offset below the starting reference.
- cols are the number of columns to offset to the right of the starting reference.
- height may be the height in rows of the returned reference (optional).
- width may be the width of columns of the returned reference (optional).
How to use excel Offset Formula
1. For example, enter the following data into a new Excel spreadsheet. Here I used a data for ABC Limited.
2. Now use the formula as =OFFSET(E6,3,2,1,1) anywhere as I9, in this case.
3. And finally, the offset result will be 900. Here, the OFFSET function in the example below returns the cell that is 3 rows below and 2 columns to the right of starting cell E6.
The OFFSET function returns a cell because the height and width are both set to 1.
Offset Function with Sum
It is well-known that the SUM function calculates the sum of the range. And you can use the Offset function with summation. For example, I have the data similar to what you see in the screenshot below.
Here, the OFFSET function returns the 2 range that is 5 rows below and 1 column to the right of starting cell E6. And using the formula =SUM(OFFSET(E6,5,1,1,2)) the result is 2300.
How To Use Excel Offset Some of the Offset Function Common Errors
If you receive an error from the MS Excel Offset Function, this is likely to be one of the mistakes:
- #REF! displays if the numerical range resulting from the required offset is invalid
- #VALUE! displays if any the supplied rows, cols, [height] or [width] arguments are non-numeric
Hopefully, you’ve got some clear ideas on the use of Offset function in MS Excel. Thank you!