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 which is 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 which can be considered as the base for OFFSET.
- 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 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 11th row after A1 (which is 2010).
Excel OFFSET Formula – Example #3
In this example, we will calculate the Sum of Last N rows using the Excel OFFSET Function. The general formula for summing up 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 SUM function along with 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 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 OFFSET function).
- We used 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 the column B. It counts all the numeric values in column B (19 is the count).
- With count as 19, the OFFSET formula becomes:
=OFFSET(B1, 19, 0, -5)
- This formula starts Offsetting at B1 with 19 rows and same column (0). Then height parameter -5 helps this function to extend the range into backward direction by 5 rows (see the negative sign associated with 5).
- The dynamic range of Last five observations is given as an input to 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 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 the lines similar to the previous one.
- OFFSET Creates a dynamic range of last three observations from column B with the help of 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 middle most 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 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 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 a data which gets added every now and then. For example, Employee Master. Your Employee Master Data will always be getting increased every now and then as there must be some new employees joining. Every time using the same formula does not makes any sense. Instead of that, create a range which is dynamic using OFFSET function and see the magic. It will certainly save you a 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. Like -negative row argument helps offsetting the data from down to up and negative col argument helps offsetting the data to the left.
- OFFSET formula may slow down the Excel functionality as it is a formula which 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 OFFSET function within it.
Recommended Articles
This is a guide to OFFSET Formula in Excel. Here we discuss how to use OFFSET Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
