Excel OFFSET Formula (Table of Contents)
What is OFFSET Formula in Excel?
OFFSET Formula in Excel gives a cell or a range of dynamic rectangular cells as an output, a subset of the total given range with a specified number of rows, columns, height, and width.
Syntax of OFFSET Formula in Excel
Explanation of OFFSET Function in Excel:
- reference: The starting point for OFFSET, or we can say a range of cells that can be considered the OFFSET base.
- rows: The number of rows to OFFSET below or above the base reference.
- cols: The number of columns to OFFSET to the right or left of the base reference.
- height: Number of rows in returned reference.
- width: Number of columns in returned reference.
The height and width are the optional parameters for this function and can be used to determine the size of the reference created.
How to Use OFFSET formula in Excel?
We will now learn how to write the offset formula to get the lookup value. Let’s understand the offset formula with some examples in Excel.
Excel OFFSET Formula – Example #1
Suppose we have data as given below.
- In cell E2, input the following formula:
=OFFSET(A1, 11, 1)
- Once done with the formula, press Enter and see the output.
What this formula did is it has taken reference from cell A1 (Year) and moved down by 11 rows after A1 (i.e. row no. 12) and then gained the value which is coordinate of the 11th row after A1 and first column (column A = 0 column B = 1 in this case. As we know, we can go both right and left), which is $ 141.27 Million.
Excel OFFSET Formula – Example #2
Now we will see another example of a reference cell for a specific year. Put the following formula in cell E3:
=OFFSET(A1, 11, 0)
- Once done with the formula, press Enter and see the output.
If you have seen the formula, it is the same as of previous; the only change is in column reference. The column reference 0 (Zero) means the value will be captured from column A for the 11th row after A1 (which is 2010).
Excel OFFSET Formula – Example #3
We will calculate the Sum of the Last N rows using the Excel OFFSET Function in this example. The general formula for summing up the last N numbers is:
=SUM(OFFSET(A1,COUNT(A:A), 0, -N))
To sum up the last N observations from your data, you can use the SUM function along with the OFFSET and COUNT function together. In this example, we will sum up the last 5 data points. Please note that when we say N, it’s a generalization of any integer value. So, you can calculate for the last 3, last 4, last 5, etc., depending on your dataset as well as your requirement.
Let’s have it done for our data set:
- Input the following formula in cell E2 of the excel file.
=SUM(OFFSET(B1,COUNT(B:B), 0, -5))
- Click Enter to see the output.
As we have already seen, the Excel OFFSET function outputs rectangular dynamic ranges when given a starting reference.
- In this case, the starting reference is provided in terms of B1 (as a first argument to the OFFSET function).
- We used the COUNT function because we need the sum of observations from the last value. COUNT Function helps OFFSET to determine how many observations (rows) are there in column B. It counts all the numeric values in column B (19 is the count).
- With a count of 19, the OFFSET formula becomes:
=OFFSET(B1, 19, 0, -5)
- This formula starts Offsetting at B1 with 19 rows and the same column (0). Then height parameter -5 helps this function to extend the range into a backward direction by 5 rows (see the negative sign associated with 5).
- The dynamic range of the Last five observations is given as an input to the sum function, which yields in summing those from column B.
Excel OFFSET Formula – Example #4
In this example, we will calculate the Median Sales for the Last 3 Years. OFFSET function can be used as an argument to calculate the same.
- Put the following formula in cell E2:
=MEDIAN(OFFSET(B1,COUNT(B:B), 0, -3))
- Press Enter to see the output.
This formula works on lines similar to the previous one.
- OFFSET Creates a dynamic range of the last three observations from column B with the help of the COUNT function (=OFFSET(A1, 19, 0, -3)).
- This can be given as an output to the MEDIAN function. Which then sorts the observations in either ascending or descending order and then selects the middlemost value as you see in output ($ 212.07).
Excel OFFSET Formula – Example #5
Here we will calculate the median year using MEDIAN and OFFSET Function.
- Use the following formula for the same in cell E3:
=MEDIAN(OFFSET(A1,COUNT(A:A),0,-3))
- Click Enter to see the output.
The same thing has happened here. The formula took up a dynamic range created by the OFFSET function with the help of COUNT. And used it as an argument to a MEDIAN function which then sorted the year values in ascending or descending order and then given output as 2017.
Usage of the OFFSET formula is in creating the dynamic ranges. It’s always better to work on dynamic ranges instead of static ones, as you might be having data that gets added every now and then. For example, Employee Master. Your Employee Master Data will always be getting increased every now and then as some new employees join. Every time using the same formula does not make any sense. Instead of that, create a range that is dynamic using the OFFSET function and see the magic. It will certainly save you time.
Things to Remember
- OFFSET function only returns a reference to a particular range of cells in Excel. It does not move the cells from their original positions.
- By default, this function works on the upper part (in terms of rows) and right-hand side (in terms of columns). However, it can be provided with negative arguments to reverse the default functionality. A negative row argument helps offset the data from down to up, and a negative col argument helps offset the data to the left.
- OFFSET formula may slow down the Excel functionality as it is a formula that gets recalculated every time there is a change in the worksheet.
- #REF error will be thrown if your range is outside the range of your workbook.
- Any other function which expects a reference is able to use the OFFSET function within it.
Recommended Articles
This is a guide to the OFFSET Formula in Excel. Here we discuss how to use OFFSET Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
