How to use an OFFSET Function in Excel?

Introduction

 

You often might be coming up with a situation while working with you day-to-day reporting that the need of the hour is a formula that could give you running totals for the last three months, running average of the last 6 months, and so on. 

 

Now, since the data is going to get added to your excel sheet every day, week, or month, it becomes pretty difficult to drag down the commonly used SUM() and AVERAGE() functions to do such tasks. 

 

You might have already started looking at some function in Excel that could move with the data you add. Haven’t you yet? Worry not, we are here with a function that works perfectly well with the moving data.

 

The OFFSET() function from Excel works really well with such situations. It is a great way to generate results based on the moving range of data (Ex. Last three months’ average every time we add new month data to the sheet). 

 

It is a formula that can be used in combination with other Excel formulae over dynamic ranges (Remember? Moving data!)

 

(Must read: Data types in Python)

 

 

Syntax of the OFFSET() Function

 

The syntax for the OFFSET() function is as shown below:

 

=OFFSET(reference, rows, cols, [height], [width])

 

Where,

 

reference - specifies the starting point which could either be a single cell or a range of cells.

rows - Number of rows we want to move down (or up) from the reference range provided.

cols - Number of columns we want to move right (or left) from the reference range provided.

height - specifies the number of rows we wanted in return.

width - specifies the number of columns we wanted in return.

 

Both height and width are optional arguments and if not provided, the system will return a single cell as an output. Meaning, the default value for them is 1.

 

Let us take a simple example for understanding the OFFSET function.

 

(Also read: Python to represent output)

 

Example 1

 

Let us assume a dataset where we have global sales values for every month of the year. 

---

Data of global sales in US$ millions monthly

---

Now, in cell D4, initiate the OFFSET() formula. Provide B4 as a reference argument, we want to move 1 row down the reference range so provide 1 as rows argument, we don’t want to move the column to left or right and hence put 0 as an argument for cols, after that, for height and width, provide 1 as an argument respectively (it will return a 1x1 array). See the formula as below:

=OFFSET(B4,1,0,1,1)

---

 

The working of OFFSET function with all arguments

---

By looking at the formula, it should return the value 299.8 as an output. Which is the first row down the cell B4, in the same column with row height and width 1. See as shown below:

---

Output image for the OFFSET() function

---

An important thing to note is, we can move the rows and cols argument on both sides. Meaning, if we want to move rows up by one position, we can set rows argument as -1, -2, -3, ..., and on similar lines, if we want to move the column to the left, we have to set cols argument as -1, -2, -3, … so one.

 

Now let us see what happens when we try to return an array of numbers as an output.

 

See the formula below where we try to OFFSET the last four months’ value for global sales using the offset function.

 

=OFFSET(B4,9,0,4,1)

 

Here, we are starting with a reference value B4, then moving 9 rows down, under the same column, returning the last four rows, and hence height is 4 and width is 1. 

---

OFFSET function to return Global Sales value for last four months

---

See the output if we get the same result. 

---

The OFFSET() function returned last four months Global Sales Value

---

Interestingly, we were applying the formula to only one cell (D7). However, the Excel system is smart enough to understand that the OFFSET() function used here is generating an array of four elements. Thus, the spilled array behavior of Excel comes into the picture. This spilling property identifies that the Excel formula is generating more than one value and then returns those into subsequent cells.

 

(Recommended blog: Scrapy Tutorial for web scraping)

 

Example 2: Using OFFSET in Combination with Other Functions

 

Now, in the second case of this example, we have used the OFFSET() function to generate an excel array of more than one cell. Well, when such cases arise, that you are using OFFSET to generate ranges as an output, it is convenient to use OFFSET with other functions that can take up the ranges as an input. For Example, we can use offset with AVERAGE(), SUM(), etc. functions.

 

We have a new set of data as given below for this case. This dataset has a two-dimensional structure with regions in columns and months in rows. Thus each cell of this two-way table represents the sales value for that particular region associated with a particular month. See the below screenshot:

---

Two-Way Contingent table with a region-wise monthly sales value in US$ Millon 

---

Let us create a situation here. I want an average of the last three months and I need it for AP (Asia Pacific) and LA (Latin America) these two regions. Also, I know that every month the data will get added for these regions based on months. So I want this formula to be smart enough to select the last three months’ sales values into consideration (I don’t want to change the ranges every time the data gets updated). Well definitely, we have to write this formula two times as the range changes. Let us first see how we are going to achieve this.

 

Step 1: First thing we would like to do is decide the range and count how many non-empty numbers are there in that range. Well, this will allow us to get the rows argument and we are keeping it dynamic so that every time we add rows up to this data, we get the latest count of non-empty numbers. The formula is pretty simple and as below:

 

=COUNT(D:D)

---

COUNT() function to count how many numbers in column D

---

This will return a number value as 12. Since there are 12 numbers in column D.

---

Output of the COUNT() function on column D

---

Step 2: Now, we are going to use this result as a rows argument inside the OFFSET function. We are keeping cols as 0 since we want value from the same column, height, we are setting it up as -3 as we want to get the last three rows in the result, the width is set to 1. Mention to not, D5 is set as a reference value under the OFFSET() function. See the formula below:

 

=OFFSET(D5,COUNT(D:D),0,-3,1)

---

Using OFFSET()  and COUNT() function together to get the last three months data

---

See the output below there are the last three elements of the AP column (Column D) shown.

---

The last three elements of the AP column offsetted

---

Step 3: Now, use the AVERAGE() function to get enclose the result of the OFFSET() function. Meaning, the AVERAGE() function will take the last three months’ sales value for AP and give the average for the same.

 

=AVERAGE(OFFSET(D5,COUNT(D:D),0,-3,1))

---

 

The AVERAGE() function to get the average of the last three months for AP

---

See the output as shown below:

---

Last three months average sales value for the AP region

---

Now, the most beautiful thing about this formula is, even if we add a row or more below to the table, the formula will always pick the last rows to generate the average. See the screenshot below:

---

The formula works dynamically to give the average sales value for the last three months

---

This is the power of the OFFSET() function. It can be utilized in combination with some other functions to work with the moving data ranges. However, on the downside, it is a volatile function that is hard to understand if you are new to the field of Excel. 

 

Besides, it every time gets calculated when you move your data and it can be slowing your sheets down if you are using it with a considerably large amount of data or even if you are using it more than often. Word of wisdom? Keep its use limited and you will be OK!

 

(Suggested blog: First Step towards Python)

 

Let’s wrap this one here with some summary Points:

 

 

Summary

 

1. OFFSET() is a great utility when you want to have calculations for moving data.

2. The output of the OFFSET() function is always a reference. Don’t think it moves cells away.

3. The function itself is volatile and can cause slowness as it gets calculated every time we move the data. This could be an issue when you are working with a really large set of data or a file with multiple calculations.

 

Download your excel workbook from here.