| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i add in a last updated function for a series of cells? | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Can I add an average series to a chart with 2 or more series? | Charts and Charting in Excel | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
