average last four rows in a column
 

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.

average last four rows in a column
 

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

"LoveKeIIy" wrote in message
...
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.

average last four rows in a column
 

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


--
Regards,

Peo Sjoblom




"LoveKeIIy" wrote in message
...
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.

average last four rows in a column
 

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

--
Don Guillett
SalesAid Software

"LoveKeIIy" wrote in message
...
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.