Bernard, I tried your solution - thank you it seems to work, but I have to
admit to not understanding the logic that well!
Any ideas where I might find an explanation of the formula?

"Bernard Liengme" wrote:

Misread question, I averaged last 6. Change that to
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-4,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0))
to average last 4

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Bernard Liengme" wrote in message
...
I think it can be done more elegantly but this works
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0))

I am assuming you will not go passed A5000

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"keving" wrote in message
...
I am sure there is a simple way!
each week I insert a number in a column, thus after 6 weeks I have A1:A6
filled in. Each week I need to take the average of the last 4 numbers.
How
can I calculate this automatically without having to change the range
each
time?
from Kevin