Excel OFFSET Function for Dynamic Calculations
Excel OFFSET Function for Dynamic Calculations
Do you want to create formulas that “move” with your raw data set?
That’s when you need to use Excel’s OFFSET function.
OFFSET is a great formula whenever you have dynamic ranges involved. Some examples are:
Calculating the average of the last three months when you add new data to your table
Getting data from the last cell in your raw data range – either last row or last column in that range
Grabbing data from a data table based on a point of view chosen by the user
Complex calculations that require “moving” data sets
3 Practical cases of Excel OFFSET are shown in this Video:
OFFSET is a difficult one to get the hang of because of the number of arguments involved.
Think of it as a GPS:
You give it a starting point and then you tell it:
- how many rows to go down
- how many columns to move across
- and what range you want returned
Excel OFFSET Function for Dynamic Calculations – Explained in Simple Steps
Offset is an interesting formula and it’s one that can do so much more than meets the eye. Offset is a way of giving Excel an address to go to.
You start off by telling it how many rows to move and then how many columns to move to get to its destination. This destination can be a single house (e.g. a single cell), it can be a street (e.g. many cells in one row or one column), or it can also be a town (e.g. an area of rows and columns).
Excel Offset: Data Arranged Vertically (0:45)
In this example, we have a table with Months in the first column and Sales Revenue in the second column.
One way of getting the average of the last six months, is to use the AVERAGE function. To do that, you highlight the cells under the Sales column.
However, the problem is that with a dynamic report such as this, there would be new monthly data coming in after August, and so you would have to manually drag the range of the AVERAGE function to refer to the most recent six months.
The OFFSET function helps you avoid having to do this!
To start, let us go through the OFFSET function on its own before we move on to integrating it in the AVERAGE function. The syntax of OFFSET is:
Let’s do a simple example:
You always need a starting point as a reference. Pick any cell that is close to the range that you want to move around in. In this example, we can select cell B3, which is the column header of the Sales column.
After that, indicate how many rows you want to move down. Let’s say I want to move down 1 row. Then specify how many columns you want to move. I want to stay in this column, I will type 0.
The last two arguments are the [height] and the [width]. Writing down 1 for height and 1 for width, means I want one cell. These last two arguments can never be 0,0. The minimum they can be is 1,1. If you want to refer to three rows, you’d write 3,1.
For this example, let’s start with using 1,1. My formula at this stage look like this:
You will notice that the formula’s result is 100. What happens is that it starts at cell B3, and goes down 1 row, then it doesn’t move columns, then it gives us one cell. This gives us 100.
If you set the row parameter to 3, it would move down three rows and give you 130.
For the column parameter, putting a -1 would move one column to the left and return the date in cell A6. Using a +1 for the column parameter moves one column to the right and returns a 0 because cell C6 is empty. This is how OFFSET works.
If we want to use a range instead of a single cell, we would specify a value greater than 1 (or smaller than -1) for the [height] parameter. Writing the syntax as:
This results in an error. Why? Let’s track what it’s doing. It starts from cell B3, moves down three cells to B6 and then it tries to give us cells B6, B7 and B8. However, it can’t actually put these three cells in one cell.
Whenever the OFFSET function is used in a way where the last two arguments are ranges, and they’re not just returning one cell but a range of cells, you need to wrap your OFFSET formula in a formula that can handle ranges. Examples of this would be the AVERAGE, SUM, or the COUNT formula.
In this case, we’re going to use the AVERAGE formula. The function would now be:
It results in 108, which is the average of B6, B7 and B8 cells. If we were to expand on this formula to get the last six months, we have to write the formula differently.
One way is by identifying the last cell in the column using the COUNT function and then use -6 as the [height], which helps us highlight our range “backwards”.
Another way is to find the last cell, then go back 6 rows and then specify the [height] as +6. It might seem confusing at the start, so let’s start with the simpler method first.
Start by using cell B3 as your reference. How many cells should we move down? Use the COUNT or COUNTA function to count the number of filled cells that we have in the Sales column right below B3.
The COUNT formula only counts if values are numbers.
The COUNTA function counts a cell even if it’s text.
In this case, it doesn’t matter if we use the COUNT or the COUNTA function.
Using the COUNT function, we highlight the cells under B3. It’s essential to highlight more cells than what you’re currently using, because you want to take into account new data that will be added in the future. The formula would now be:
This is a breakdown of the formula:
- Reference is cell B3.
- Row parameter uses COUNTA function which results in a 7. This tells the OFFSET to move 7 cells below B3.
- Column parameter is set to 0, which means it stays in the same column.
- [Height] parameter is -6 because we want to take the last 6 cells (move backwards). In this case, it will highlight cells B5 to B10.
- [Width] parameter is 1 by default.
This formula results in 113.
Now, when we add data for a new month on cell B11 – let’s say, 200 – the formula automatically updates to an average of 127, which we can check by comparing it to the average displayed on the status bar when cells B6 to B11 are highlighted.
We can also check this by going to Formulas > Evaluate Formula, and then click on the Evaluate button. You will see that the final range that it’s taking is the average of B6 to B11, which is what we intended.
The other way of writing this average offset formula is:
The breakdown of this is:
- Reference is set to the first data value, B4
- Row uses a COUNTA function which counts all the filled cells from B4 to B14 and subtracts 6.
- Column is 0, indicating no movement or shift of columns.
- [Height] is 6, which highlights 6 cells (going downwards).
- [Width] is 1 by default, since we only need one column.
This results in 127 as well. These are two different ways of writing the formula.
Let’s take another example where the data is arranged in a horizontal manner.
Excel Offset: Data Arranged Horizontally (9:01)
To perform the same computation, we follow a similar approach. We start with the AVERAGE function followed by the OFFSET function. We write it as:
which translates as:
- Reference set to A17
- Row set to 0, indicating no movement. We want to stay on the same row.
- Column parameter uses the COUNT function which counts the number of cells with values from columns B to O. Column O is an estimate of where you think you will have numbers. This part gives us a 7.
- [height] is set to 1 since we are referencing only one row.
- [width] is set to –6, which highlights 6 columns to the left of the last completed cell (goes backwards).
This achieves the same as getting the average of the last six months from July. If we again add data for August, it automatically updates to include August and then computes the average of the last six months. This results in 127.
Just be creative and practice this in all directions until you get the hang of it.
Excel Offset: Dynamic Range for Moving Average Calculations (10:42)
Another good example is the case where you allow the user to select the month and your formula provides the average of the next 3 months.
This is your raw data:
To perform this computation, we’re going to do the same thing by using the AVERAGE function together with OFFSET.
Let’s fix our starting point to the column header, B25.
For the row, we want to move down the number of cells to arrive at the selected month that the user picked from the drop-down. The MATCH function would give us the position of the selected month in the data table.
For example, if the selected month is 04/2014, which corresponds to 90, the formula needs to find the position of 90 in the data table, which is the fourth row. To use the MATCH() function, the syntax is:
We refer to the drop-down cell, F25, as the lookup_value, and the Month column A26:A51 as the array. [match_type] is set 0 for a perfect match. The formula will read:
This would count the position of the selected month. In the case of 04/2014, it results in 4.
Column can be set to 0 because we want to stay in column B.
[Height] depends on the question. If we want to include the selected month in the computation, we would use a 3.
[Width] is set to 1 .
The resulting formula is:
which yields 102.
If we do not want to include the selected month in the computation, we have to add a +1 to our MATCH function. This tells the formula to find this selected month and go down one extra cell. The final formula becomes:
The average yields 113, which is the average of the three months after the selected month, i.e. average of B30:B32.
This is completely dynamic so if you were to select December 2014, you would get an average of 232 for the three months after this month.
This is how you can use the AVERAGE function, the OFFSET function, and the MATCH functions all together to create this dynamic effect in your Excel files.
Caution!
A word of caution is that the OFFSET is a volatile Excel formula, which means it calculates every time you make a move in Excel. To be honest, in the files that I’ve used OFFSET in, I’ve hardly experienced my files becoming slow because of using this function. But I also didn’t overdose on them. I recommend that you don’t overuse them.
Download the Workbook HERE.
ليست هناك تعليقات:
إرسال تعليق