average last four rows in a column

L

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

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top