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.


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.


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,


with the same result.

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

Comments are closed.