Sign in or start a free trial to avail of this feature.
10. Updating Formulas with New Data
When you add additional records to a dataset, you don't have to update all the formulas each time. Instead, you can automatically grow the arrays when new records are added.
The Problem of Updating Data (00:30)
When datasets update, formulas that use them need to be updated too. Doing this manually is highly time consuming. Instead, we want to find a way to update the arrays used in our formulas to include new entries. We can do this using a strategy that combines the OFFSET and COUNTA functions.
We consider a simple example where we have several months of revenue figures. We have a lookup cell which uses INDEX and MATCH to find the revenue for a given month. However, when we add a new month of revenue to the dataset, the INDEX and MATCH functions won’t automatically recognize the new month.
The OFFSET Function (01:02)
OFFSET is a complex function with many inputs. As a result, it’s best not to include it in formulas. Instead, we’ll create named arrays using OFFSET, then include those arrays in our formulas.
In our example, we create an array representing the column of months, and an array representing the column of revenue figures. By using OFFSET, both of these arrays will update when we add new values in their columns.
We start each array using the Name Manager. In the Refers To box, we enter the OFFSET function. OFFSET returns an array that is a certain number of cells away from a reference point. OFFSET takes five arguments, as follows:
First is the reference cell. This is the value from which the offset is based. In our example, we use the column header as the reference.
Second and third is the row and column offset from the reference. This indicates where the output array is in relation to the reference. Our array is directly below the column header, so the rows value is 1 and the columns value is zero.
Fourth is the height of the output array. In our example we find the height of the array using COUNTA. This finds the number of non-blank cells in a specified range. For example, using the months column as an input, this returns the number of months in the dataset. We subtract one from this value to account for the column header. This use of COUNTA ensures the size of the array updates when we add or remove data.
Fifth is the width of the output array. In our example, this is simply one.
Results of the Formula (03:01)
Once we’ve created the arrays based on the OFFSET formula, we use them in our simple lookup example and find that the formula will now work when new months are added to the dataset.
The OFFSET function can seem difficult, but you can check if you’ve used it correctly. Just return to the Name Manager and select one of the arrays you created. If you select the Refers To box, the cells contained in the array will be highlighted, which lets you see if you’ve implemented it correctly.
In companies, most data sets are updated over time with new records, and as a result, the formulas for these data sets must also be updated.
As you can imagine, updating the size of our arrays in formulas can be really time consuming, particularly if we have a lot of calculations as we do in our search panel.
Wouldn't it be great if the arrays in our formulas could automatically update to include new entries? Well thankfully, they can, using a command called OFFSET.
Let's start off with a simple example that highlights the problem.
I currently have revenue for 4 months of the year, and a simple lookup using INDEX and MATCH that returns the revenue for a given month.
Unfortunately, when I add May to the array by typing the month and a new revenue figure, it won't work in the lookup.
I'd like my lookup value to update automatically when I add a new month to my data.
So let's add OFFSET to our lookup equation.
Unfortunately, the OFFSET formula has quite a few inputs.
So to make our formulas more readable, let's create named arrays with the OFFSET formula included there, and then we’ll include these names in our formulas.
So I'll create a new name with Ctrl + F3, Alt + N, I'll simply write MONTHS.
And then in the Refers to box I'll include my OFFSET formula.
The OFFSET function simply returns an array that is a certain number of cells away from a reference cell.
And we start the function by writing “=offset”, and we first include our reference cell, which in this case is month.
Next we include the position of the array, which is one row down from our reference cell, and zero columns across.
Next we include the height of the array.
And to do this I'll use the COUNTA function which counts all the non blank cells in a column.
And then I'll select the B column with Ctrl + Shift + Down Arrow twice.
The height of our array will then need to be reduced by one to account for the first row, which just includes the headers.
And lastly I'll include the width of the array, which is simply one.
I'll close the bracket and press OK.
I'll now create another offset for revenues.
Let's call this MONTHLY_REVENUES.
Again “=offset”, I'll start by selecting my reference cell, again the position of the array is one row down and zero rows across.
I'll use COUNTA, this time to count all the non blank cells in C, I'll take one away, and the array will be one column wide.
I'll close the bracket and press OK.
Now let's remove the arrays in our index formula and replace it with our named arrays.
So this will be months, and this will be monthly revenues.
And when I press return, we can now calculate the May revenue.
What's more, if I enter a June value, the array automatically updates to include June.
As you can see, the OFFSET formula is very powerful, but there are quite a few inputs to enter into the function.
Don't worry about learning these all for now because I’ve included them in the short notes below the video.
If at some stage you want to check if you used the OFFSET function correctly, simply go back into the Name Manager, and select MONTHLY_REVENUES and then click in the Refers to box.
And this will show you the cells that are currently highlighted by the array.
And here we can see that we’ve implemented it correctly.
Our final task in this course will be to apply the OFFSET function to our sales data set that includes a new transaction that I’ve added for the sales person Baines.
However, I'll leave this until the next lesson.