Forget it.

4 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-4,,-1)
3 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-3,,-1)
2 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-2,,-1)
1 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-1,,-1)

"Greg" wrote:

Hi Biff.

Thank you for the formula. Unfortunately, now the scope has changed.
Instead of a 4 week average, they want the previous 4 weeks individually
(last week, 2 weeks ago, 3 weeks ago, 4 weeks ago)

I couldn't follow the formula you created to know where to change numbers.

Since I know it's possible to pull an average over the 4 columns, I assume
it's also possible to just designate the proper columns. But, that's where I
need help.

TIA,

Greg

"T. Valko" wrote:

Assumes there will *always* be at least 4 values entered in the range and
the data is in a contiguous block (no empty cells within the range).

=AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4))

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
I am trying to figure out how to automatically average the last 4 columns
in
a worksheet that every week adds a column. In other words, for
"Prior4Weeks"
on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5)

But then, a week later with another week's data summarized in a column
added
to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5)

I know it's gotta be a macro, but don't know where to start.

TIA,

Greg