Try this:

=IF(COUNT(A:A),AVERAGE(A65536:INDEX(A:A,MAX(1,(COU NT(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.)

--
Biff
Microsoft Excel MVP


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

"daddylonglegs" wrote:

Try

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

confirmed with CTRL+SHIFT+ENTER

"bumpo" wrote:

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.