Sometimes we spend time setting up beautiful spreadsheets only for us to have to add rows or columns afterwards, which then messes up our formulas and we have to change them. In this post, we’re going to look at a couple of examples of the OFFSET function, which will help us create more dynamic formulas. What we mean by this is that the formula will adapt to changes made to the spreadsheets, quite often where rows and columns have been added.
Example 1 – Creating dynamic ranges to maintain an average formula
Here we have some marks for some students. (To those who have been following my posts, honestly, I’m not obsessed with exam marks, they just make good examples!)
In cell B6 I’ve added an AVERAGE function to work out the average of the marks.
But I now have another student to add who’s done the test. I add a row and insert the student’s details, but as you can see this hasn’t changed the average figure.
If we look at the formula, it hasn’t changed despite there being an extra row.
We can solve this by using the OFFSET function in the AVERAGE one.
In cell E6, I’ve added the following formula:
OK, so what’s happening? Well let’s look at the syntax of the function to understand it better.
The OFFSET function has 3 main parts:
cell reference: this is the cell you start from
offset rows: this is the number of rows you move to; positive numbers move down and negative numbers move up. In other words, a positive number increases the row number and a negative one decreases it.
offset columns: this is the number of columns you move to
There are 2 other optional parts, height and width, but here we’re not going to use them.
So going back to our formula:
The OFFSET function starts at E6 which is where our total is.
Then moves up one row to E5 as there’s a -1 in the second part.
It doesn’t move from the column, as there’s a 0 in the third part.
So, this returns the cell E5.
Now we just add the AVERAGE part. It takes the range from E2 to the result of the OFFSET function, which is E5 (E2:E5).
As we can see it returns the correct average like we saw earlier.
Now let’s add the extra student and see what happens. Ah-ha, the average has changed from 8.5 to 8.8, which is what we want.
Looking at the formula, we can see that it has changed subtly, the start cell reference is now E7 (the total) and it’s still moving one cell up, so returns the cell E6. This means the range is now E2:E6, which is what we want.
We can add or delete rows and the average will always be correct, without having to manually change it.
Example 2 – Dynamically calculating the sales for the last X months
Here we have a company’s sales from January to June. The sales manager wants to be able to find out the sales for the last X months. Here he adds the number of months he wants to look back from the last month, e.g. in cell D1 he writes 3. Then in cell D2 it tells him the total number of sales in that period, which in this case is 2,100 (700+600+800).
So, how did we do that? In cell D2 is the following formula:
Let’s break it down and start with the OFFSET function.
B2: This starts from cell B2 (the first month’s sales).
COUNT(B2:B)-D1: Then it counts how many rows (months) there are from B2 to the end of column B. Then it takes away the number of months we want to report back, in this case 3. So it offsets by 3 rows (6-3), so starts from cell B5.
0: It doesn’t move columns.
D1: The height is the figure in D1, i.e. 3 rows. So it takes figures that are from B5 to B8 (i.e. 3 rows).
1: It returns just that one column.
So, when we change the number of months in cell D1, it returns the new number of sales. In this case, the last 2 months total 1,400.
This has the added benefit, like we saw in example 1, that when more rows are added it still works. Now, the sales manager has added the sales for the month of July. As we can see, the last 2 months now add up to 1,800.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store