Hi Mike.

Thank you for the formula, upon data checking, there's a problem. It
doesn't return the correct number.

Here's the formula I'm using and the data:

=AVERAGE(OFFSET(INDEX('Prior Weeks'!6:6,,COUNT('Prior Weeks'!6:6)),,-4,1,5))

Calls Received/Daily Avg 642.75 591.20 563.80 607.40 590.00 604.25
662.20 530.80

The number returned should be 596.8125, but with the formula, it returns
605.530.

Any ideas?

TIA,

Greg


"Mike H" wrote:

Ah,

I misread your post but it still doesn't need to be a macro

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

Mike

"Mike H" wrote:

Hi,

No it doesn't need to be a macro, try this

=AVERAGE(INDIRECT("sheet2!A"&MATCH(6.0222*10^23,Sh eet2!A:A)&":E"&MATCH(6.0222*10^23,Sheet2!A:A)))

Mike

"Greg" wrote:

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