average function for a series

[bumpo]

Using Excel 2000.
I'm looking for a function that will look at a database , lets say, A1:A100
and take the average of the last 5 cells, A96:A100. When I add data to A101,
I want that same function to average A97:A101.

 

[daddylonglegs]

Try

=AVERAGE(OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-5,,5))

confirmed with CTRL+SHIFT+ENTER

 

[Teethless mama]

=AVERAGE(INDEX(A:A,MATCH(10^10,A:A)-4):INDEX(A:A,MATCH(10^10,A:A)))

 

[Teethless mama]

Your formula doesn't required ctrl+shift+enter. Just press ENTER

 

[Anu]

Please try =AVERAGE(OFFSET($A$2,COUNTA($A:$A)-6,0,5,1))

Anu

 

[bumpo]

All the replies were great and all worked. I also need to average the last 10
cells and the last 15. Not sure what to change. In Daddy's formula, I changed
to -10,,10. but I get a #REF! until my data base has data in all 10 cells.
If the data base is only 9 or less cells, I would like the function to
reflect the ave. of those. Is this possible?
Thanks in advance.

 

[ryguy7272]

=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-4,0,5))

Regards,
Ryan--

 

[T. Valko]

Try this:

=IF(COUNT(A:A),AVERAGE(A65536:INDEX(A:A,MAX(1,(COUNT(A:A)-n)+1))),"no data")

Where n = the number of cells you want to average: 5, 10, 15, whatever. If
there is less than n numbers it will average whatever's available.

I'm assuming the data is in a contiguous block (no empty cells or TEXT
entries within the range.)

 

[bumpo]

Biff
Thanks for your response. This is huge help. The way I get what seems to be
the correct results is to use n = (the number of cells to average) -1. eg.
5-1, 10-1, 15-1
Does that sound right?
Thanks again,

 

[T. Valko]

Post the exact formula you're using. The adjustment you made is probably
based on a different range starting point.

 

[bumpo]

I think you're right. My db starts in B2 and goes down the column.

 

[T. Valko]

Ok, that's it. In your original post you said the range was A1:An. So I used
that as the basis of my formula.