Put an OFFSET Formula Inside a Named Range

In the last post I used the OFFSET Function inside the AVERAGE Function to return the last 7 days of a range. Here I’ll create a Named Range using a formula with the OFFSET Function.

I use a worksheet to inform me of the Last 7 Day Average for Over-Under Calories data, as shown below.

OFFSET Function Examples

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.

Create a Named Range with the OFFSET Function

I want to take everything inside the AVERAGE Function and put it inside a Named Range, which I’ll call LastSeven. The key to this formula is that range references have to be absolute and the worksheet name included.

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7)

In Excel 2010 go to Formulas, Name Manager, and click New.

OFFSET Formula inside Named Range

Type in the Named Range LastSeven, enter the formula listed above, =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7) then type a description if you like and click OK.

Now the previous formula can be revised to include the named range LastSeven,

=AVERAGE(LastSeven)

with the same result.

1 thought on “Put an OFFSET Formula Inside a Named Range”

Comments are closed.