average function for a series
 

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.

average function for a series
 

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.

average function for a series
 

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


"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.

average function for a series
 

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

"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.

average function for a series
 

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

Anu

"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.

average function for a series
 

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.

average function for a series
 

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

Regards,
Ryan--

--
RyGuy


"Anu" wrote:

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

Anu

"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.

average function for a series
 

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.

average function for a series
 

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" wrote:

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.

average function for a series
 

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

--
Biff
Microsoft Excel MVP


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

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.

average function for a series
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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.

average function for a series
 

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.

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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.