automatically average the last 4 columns

[Greg]

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'!A55)

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

 

[T. Valko]

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

 

[Mike H]

Hi,

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

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

Mike

 

[Mike H]

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

 

[Greg]

Biff and Mike:

Thanks for the help! You're great!

The cells should have data, if not, does it grab data from the last 4 data
points?

Also, does it matter if the data is Zero? (Not null, but 0)

Thanks again!

Greg

 

[RagDyer]

This *array* formula will average the *last* 4 entries, even with blanks
in-between, AND,
will average any available values if less then 4 are entered.

So, at the beginning of the year, where there's less then 4 weeks data, OR,
if you're closed for 2 weeks vacation with no data, this might be a viable
formula to try.

Say your range is sized for 52 weeks:

=AVERAGE(INDEX(A5:AZ5,LARGE(COLUMN(A5:AZ5)*(A5:AZ5<>""),4)):AZ5)

 

[Mike H]

Glad that helped,

If you want to use the last 4 populated cells and ignore zero try this

=AVERAGE(INDEX(5:5,LARGE(COLUMN(5:5)*(5:5<>0),5)):IV5)

I've done it for the active sheet but you should be able to convert it for
sheet 2

Mike

 

[Greg]

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

 

[Greg]

Forget it, I used a different formula and it worked:

=AVERAGE(OFFSET('Prior Weeks'!B7,,COUNT('Prior Weeks'!7:7)-1,,-4))

 

[Greg]

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

 

[Greg]

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)