Use the OFFSET function for tabulating moving data

- [Voiceover] Excel has an unusual function called offset. If you go to the formulas tab in the ribbon, you might have discovered it if you click the Look Up and Reference portion and you will see Offset, its description, Returns a reference to a range that is a given number of rows and columns from a given reference. That gives you some sense of the capabilities, but let's show it in a real life example. On this worksheet I've got a list in column A. It's been converted to a table. It's not a prerequisite for what we are about to do here, but it does suggest the idea that we've got a chart to the right, and as the data grows, the chart will expand automatically.

We're tracking data for nearly every day of the year here. Except for certain holidays, there are sales every day. The list is grown. If I click on any cell in the list and double click the bottom edge, you'll see that currently we've got data through May 15th. So let's say we update this, if not necessarily every day, update it every few days so that we have all of our totals in place, and at any given time we want to know what the latest date of entry is and the latest sales, and we can do this first of all not by the offset function, we can use a function called index.

And I'll zoom in a bit on this so we can see it a bit better. Holding down the Control key, using the mouse wheel, let's zoom in a little bit. Now, before doing that, I want to point out a function we'll be using in two different examples here. It's called counta, you might be familiar with it. Counta simply says, I'm looking somewhere, for example, column A, and no way am I saying that counta only works in column A, it works in any location, of course. We're looking in column A, how many cells there have data? Think of counta as meaning perhaps being count-alphanumeric.

Count any cell that has any kind of data in it, how many of those cells have data? 136, that's viable information. If I click over here and double click the bottom edge, we can certainly verify that. We've got data down to row 136. Double click a top edge, we're back at the top. So keep that thought in mind. Maybe I'll just put as pace in front of that, and leave it that way to remind us. We're gonna be using that in this function. And first of all, we could be using a function called index. You might be familiar with this. It's likely to be shorter in the example we're using here, but it doesn't have the flexibility of offset for what we're trying to eventually do here with this data.

The index function says, I've got a table somewhere. Imagine if that table is only column A. Click column A, comma, which row number do we want to go here? Let's use that counta function, that tell us how many rows we've got. Let's jump down to that location, comma. Index expects a column number reference, but since the table has only a single column, we don't need that. I'll simply backspace, put in a right parenthesis, and we will have an answer here.

Find the last entry in that table based on the number of rows that we have. And there it is, it's May 15th, that's our last entry. And we should be able to copy this into the cell to the right, because we want to do the same thing here, but for column B. So this formula is all about column A, we'll drag it rightward, and this is for column B. Obviously that's not the answer we want because it copied the format as well, so a quick fix here could be from the Home tab we'll simply click comma, that's one way to get there, and verifying that, we'll click in column B, zip to the bottom with a double click, and it's 29,102.

Double click the top edge, we're in shape. Now the test for this will be if we change that last entry, and to make this a little bit simpler here, so I'm gonna zoom back a little bit and hide most of the rows, let's say from row nine downward, and let's say we keep the last few rows visible here. That many is good enough for now. We'll hide all these rows, and there we are, we can see the data a little bit better. I'm about to drag this downwards so that we will see 5/16, and that should pop up in cell D2.

You might get a message along the way about changing a table, that shouldn't be a problem. There we are, we didn't, it's okay. So, 5/16, that's working fine. I haven't changed the number over here yet. I'll type it in, it's 29,305, but as I press Enter, the entry in cell E2 should change as well. Now, we've used in both cases here the index function, but let's use offset, and first this will not be impressive. In fact it will say, why would we use this because index already does the job? It's because we want to expand this to do other things as well.

Equal offset, left parenthesis. Cell A1 is our starting point, I'll just type it in, comma, and now what? We want to move downward how far? We want to move down to row 137. That means we want to move downward from that point 136 rows. Now, counta which we used earlier and we're going to use again, if we use counta on column A, that's gonna give us the value 137. So we want to subtract one from this, so we want to move down that number of rows.

We don't want to move left or right any number of columns. That's the next reference that we see in the pop-up tip below, so we put in zero. And height and width, which are optional entries, we don't need to use just yet. So all we're doing here is simply creating a way to get the latest date, but this time by way of an offset function, which I admit is not better necessarily and certainly longer than we saw earlier with the index function, but we'll get the same answer as before. And here too, we can copy this rightward, copy the format from above downward using the right mouse button, down here, copy it as formats only, and we've got our answer there.

But suppose we want to do an average, for example, of the last five days. In other words, what we'd like to be able to do looking at all this data, remember, I have hidden rows temporarily here. How about the average of the last five days? In other words, let's get the average of these five numbers right here, or the last three. And we want to be able to do that without necessarily hiding the rows. That was just done for convenience sake here. But we need the offset function here. The index function is not gonna help us do this. It's a little tricky at first, but we will be using those additional capabilities of offset.

Equal offset, left parenthesis. Our starting reference is going to be cell B1, comma, and we're trying to count how many entries there are in column B. We want to move down so far, but this time we don't want to move to the bottom. If we're trying to average the last five days, we want to move down to cell 133. And so that means we want to use counta on column B minus the number of days we want to move downward, that's minus five, comma, we don't want to move left or right any number of columns.

But now the size of the range that we're picking up is five rows deep, that's what the word height refers to, that's the five, and what's the width of this range that we're trying to pull out? It's one column wide, right parenthesis. And what are we ultimately trying to do here? An average, so right after the equals sign, average, left parenthesis, and a trailing one on the right as well, Enter, there's an answer. Now if I highlight the last five cells, look in the status bar, we see 28,370.

That is correct, we're not seeing pennies in the display because the data in column B doesn't have any pennies. If I were to click column B momentarily here and on the Home tab click comma, readjust the column width, then highlight those last five entries there, look at the average that we see in the status bar, 28,369.80, same number we're seeing here. So let's again review this as we look at offset. First of all, we're trying to do an average of whatever we get here.

The offset function says let's begin in cell B1 and let's move down the column. How many entries do we have here? That's 137 minus five. Let's start by moving down 132 cells. If we move down from cell one 132 cells, we'll be at cell B133, and at that point, we're not moving leftward or rightward, that's why it says zero, that refers to columns. We're not moving left or right into column A or column C.

And what's the range we're trying to pick up here? A range that's five rows deep by one column wide. And then what are we trying to do with all this? We're trying to get an average. Now, I'm going to copy this downward. What if we want to do this for three days? What do we need to change here? Our starting point is B1, everything else here is the same, except the two fives become threes if we want to do this for the last three days. And 28,960.33, let's highlight the last three entries here, and what do we see in the status bar at the bottom of the screen to verify our total? 28,960.33.

So you can imagine possibly having some numbers down the side here showing an average for the last 10 days, seven days, five days, whatever, that sort of thing, using this combination of functions. So the offset function gives us a lot of capability in being able to essentially refer to a range and move it away from a starting point, and then by using average as we saw in the example here, creatively coming up with more information. So the offset function is a powerful tool as we see in this example. And let's test this a little bit too by adding in an additional date.

I'm gonna drag the 16th downward, maybe two dates. 17th and 18th, you might get a message like this, simply ignore it. So the dates are both adjusted using either of our techniques, either with index or with offset, we've got that adjusted. I haven't added the new totals just yet, so right here I'll put in 29,987. Watch the numbers change over in cell E2 and E3, those both changed, as did our averages, and for 5/18, down in cell B139, I'll put in the number 30,456, and that again adjusts our average.

Another quick check, let's highlight the last five entries here. What's the average we see at the bottom? 29,464.80, we also see that in cell D6 right there. And our last three right here, the average 29,916, that's what we see in cell D7. So a creative use of the offset function along with counta, and earlier we saw an example of how we might use index. And by the way, the chart all along here looks pretty strange because we've got hidden rows. That chart, because it's based on this table over here, will grow with the data.

So as I drag across the row numbers eight and 131, simply right click and unhide, that chart will shape up and it will have reflected new data off to the right, going well beyond where it had before. And so we've seen a creative use of how to use the offset function in combination with counta and the average function here in a creative way to tabulate a moving set, a dynamic set of data.