average last four rows in a column

[LoveKeIIy]

I need some assistance in creating a formula. I need to know how to create a
forumale that will average the last four enteries in a column. The problem I
have is the range to be averaged will change week to week. The sheet is an
inventory counts, and each row is that weeks use of inventory with the most
recent week being the lowest physical row (or highest numbered row).
Ie...first avg row 1-4, next avg 2-5, next 3-6, and so on.

I can average four rows, but can not get the range to change automatically.
The formula is in another sheet to show the last four weeks average useage.

 

[T. Valko]

Try something like this (assumes no empty cells within the range):

Numbers starting in A2 on down:

=IF(COUNT(A:A)<4,"insufficient data",AVERAGE(OFFSET(A2,COUNT(A:A)-1,,-4)))

If there aren't at least 4 values to calculate you'll get: insufficient
data.

Biff

 

[Peo Sjoblom]

With the values in column A

=AVERAGE(A1000:INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000<>""),4)))

needs to be entered with ctrl + sift & enter

 

[Don Guillett]

try this for col N
=AVERAGE(OFFSET(N1,MAX(0,COUNTA(N:N)-4),0,4,1))