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.

=AVERAGE(OFFSET(B1,COUNTA(B:B)-7,0,7)

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.

*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*

**Note:***argument.*

**rows**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.

Comments on this entry are closed.

{ 2 trackbacks }