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