In this example I’m going to use the OFFSET Function to return a range reference to the last 7 cells in a column. Of course, it will be inside the AVERAGE Function so I can see an average for the last week.
Sound complicated? Not really, it’s just an extension of something I did on a previous post for the OFFSET Function.
The example data is a spreadsheet I use to log my over-under calories to goal each day. This data is downloaded periodically and currently the data goes to row 269, as you can see below.
I use the OFFSET Function at the top of the worksheet to inform me of the Last 7 Day Average for the Over-Under Calories data, as shown below.
Cell E2 has the following formula.
The AVERAGE Function is merely taking the range reference provided by the OFFSET function to return an average. We’ll focus on the OFFSET Function and start with the arguments.
The OFFSET Function Arguments
The OFFSET Function has three required arguments — reference, rows, cols — and two optional arguments — height, width.
OFFSET(reference, rows, cols, [height], [width])
Reference is the anchor point, or ground zero. Everything starts here.
Rows is the number of rows from the anchor point. Or the row offset. A positive number means down from the anchor point and a negative number means up from the anchor point.
Cols is the number of columns from the anchor point, or the column offset. A positive number means to the right of the anchor point and a negative number means to the left.
Height is the number of rows you want for the height of the range; a positive number, and if omitted is the same height as the reference.
Width is the number of columns you want for the width of the range; a positive number, and if omitted is the same width as the reference.
The OFFSET Function and the Last 7 Data Points
It’s useful knowing the 7-day average for over-under calories to goal. The OFFSET Function can give me this if I use it inside the AVERAGE function.
For the OFFSET Function, the first argument reference is B1. That also means row 1 and column B when you break it down.
The second argument rows is COUNTA(B:B) -7, which computes to 269 – 7 = 262. The function COUNTA(B:B) is counting all non-blank cells in column B. Note: It’s important to remember that if any blank cells are in column B, the COUNTA Function won’t provide the right number to the rows argument.
Using 262 as the rows number, counting down from row 1 (the reference row) will plant you squarely in row 263, as the first row is not counted.
The third argument cols is zero. We’re not offsetting left or right.
The fourth argument height is 7. From the second argument we have row 263. Counting down 7 rows gives us the range reference B263:B269.
The fifth argument width, is omitted because we only want one column for the range.
The OFFSET function returns the reference B263:B269, and that leaves the rest of the formula as AVERAGE(B263:B269) for this example, which results in the answer 28.964.
The formula in this example will always give and average for the last 7 data points in column B, as long as there are no blank cells.