=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2 :CL2)-COLUMN(D2)-5))):(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2:CL 2)-COLUMN(D2)+1)))))

this is an array formula, so commit with Ctrl-SHift-Enter


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
I have a sheet with data in columns.

The field starts in D2 and continues column by column with a new
column each week.

Currently data is populated through AL2 but will continue all the way
to CL2. In CN2 I need a formula that will calculate the average of
the last 6 entries.

Currently that would be AG2:AL2 but next week that would need to be
AH2:AM2 and so one.

The columns will populate as data loads and I need this dynamic
average to automatically update.

Your assistance is greatly appreciated

Don Flak