11. Updating Formulas with New Data Part 2

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

To finish off the course, we update our search panel formulas to automatically include any new company records added to our sales dataset.

Summary

  1. Updating Revenue and Salespeople (00:24)

    In the sales dataset, a new transaction has been added. We aim to update all the formulas in the search panel so that they will automatically adjust to include new data. We do this by creating named ranges in the sales dataset using OFFSET and COUNTA, following the principles of the previous lesson.

    In the Name Manager, we update the existing range for Revenues so that it includes all non-blank cells in the Revenue column. We update the existing Salespeople range in the same way.

    Once we do this, we can update the calculations in the Sales by Sales Rep panel. We replace any hard coded array references with references to the named arrays. This updates the calculations to include the new transaction, and ensures that all future transactions will be included automatically.

  2. Updating Company Names (03:40)

    In order to update the top 5 list of companies, we need to update the named array representing company names. This follows the same principle as before. We update the array using OFFSET and COUNTA. In this case, the top 5 list already referenced the named array, so the formula updates as soon as we update the array.

  3. Updating States (04:43)

    When you combine named arrays with standard arrays, errors can occur. In our search panel, the State section starts returning an error when we update some of the named arrays. For this reason, it’s best to create offset arrays at the beginning of your analysis if you know that the dataset will be updated in the future.

    In our dataset, we create a new named array for States. This uses OFFSET and COUNTA, just like the other named arrays we’ve created. We then update the various formulas in the State panel to use this named array, and this fixes the errors in the formulas.

Transcript

In the last lesson we learned how to automatically extend our formulas to include new data. Using the offset command, we can now add July to this list, give it a revenue, and then perform a lookup that will automatically include July.

Let's now apply this functionality to our sales data set.

I've added a new transaction for the company Open Roads, which is the largest transaction in the data set, and assigned to the sales person Baines, but unfortunately it’s not appearing in our search panel.

So as we saw in the previous lesson, the best way to add offsets to a data set is using named ranges, because it keeps the formula much more readable in our search panel.

Starting with the sales rep panel, we'll need to make changes to the revenues range, the sales people range, and the company name.

So we'll press Ctrl + F3 to bring up the Name Manager box, and we’ll start with the named range revenues.

I'll press F2 to enter the Refers to box, and I'll move the Name Manager just to the right so you can see the column in question.

I'll now update the formula with “=offset” and I'll first select the reference cell, which will simply be the revenue header.

Next I'll select the position of the array, which is one row down and zero columns across.

I'll then enter the height of the array, which will be COUNTA, the full C column, and then minus one to remove the header.

And the array will be one column wide, and we’ll close the bracket and finish.

Let's now update the sales people range.

So again I'll press F2, and I'll write “=offset”, and then select the reference.

Again, I'll just move the Name Manager box to the left so you can see the column in question.

And the reference will be the sales person header.

Next I'll put in the position of the array, which is one row down and zero columns across.

Again, I’ll use COUNTA to count the height of the array, and we’ll simply select the sales person column and we'll take one away.

And finally, the array will be one column wide.

So we’ll close the bracket, press enter and ESC.

And now we can update two of the calculations in our sales rep panel.

I'll press F2 to enter the formula, and replace the existing array with sales people.

And the number of customers has now been increased from 11 to 12.

Let's also make this change for total sales.

Press F2 to enter the formula, and the first array will be replaced with revenues.

And the second array with sales people.

And the total sales has increased accordingly.

Now let's make the change for our top 5 customers.

Again, we enter the formula with F2, the first array is replaced with revenues, second array with sales people, press Ctrl + Shift + Enter because this is an array formula.

We then copy this formula for the remaining 4 cells.

Unfortunately, this has caused errors in our top 5 customers, so we’ll need to change that formula as well to fix this.

So we must first update the company’s name, named range, so I'll press F2 to enter the Refers to for this formula, “=offset”, again the reference will simply be company name, the array is one row and zero columns away.

COUNTA, the whole column, minus one and it’s one column wide.

And then press Enter and Esc to finish.

You can now see that the top 5 customers have been updated and Open Roads is ranked number one, which proves the formula is calculated correctly.

What's more, if I add another row to my data set off camera, and then return to our search panel, this new company is now also included in our formulas.

You might have noticed that an N/A error appears in the State panel when we updated our arrays.

And this often happens when you have offset and named arrays combined with standard arrays.

If you have data sets that you know will need to be updated in the future, the best policy is often to create named arrays with offsets for all columns in the data set, before you start building your formulas.

Anyhow, let's quickly fix this by creating a new named array for the State column So I'll press Ctrl + F3 to bring up the Name Manager, and Alt + N for a new name.

I'll call this STATES, and I'll go to the Refers to box and create an offset formula.

My reference as always will be the header, the position is one row down and zero columns across.

And the height of the array is COUNTA, select the full column, close the bracket, minus one, and the array will be one column wide.

Press Enter and now we have our new name in place.

We now need to put this new name into our formula.

So I'll press F2, and then in this first array we’ll replace it with States.

Ctrl + Shift + Enter and we’ve now fixed the error.

Unfortunately, the formula is still referencing the wrong top customer.

I’d like this to be Costa Airways.

So we’re going to need to update some of the other formulas in the State panel as well.

Let's start with this number.

So I'll enter the formula and I'll replace this with revenues.

I'll replace the next array with States.

Ctrl + Shift + Enter to finish, and now we are referencing the right top customer.

I'll quickly update the total sales and the number of customers for this panel as well.

So again, I’ll enter the formula, change this to Revenues, and change the next array to States.

And I'll do it one more time, placing this array with States.

And now this panel is completed.

As an exercise, try to replace the arrays in the sales by month and sales by day of the week panels with offset, testing with the April month which is when the Open Roads transaction occurs.

With this course on lookups and database functions now complete, you should be more than ready to move onto more complex analysis of data, safe in your ability to pull any information you need from the records in your data set.

Excel Excel for Business Analytics Learning Plan
Lookups and Database Functions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial