Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average the first nine numbers in a range.
I am trying to compare the average for the first nine months of 2007, to
2006; once october is over this will change to the first 10 months of 2007 and so on. I have an automated cell that counts the number of months that have passed in 2007, and another that takes the average of the the 2007 results. The problem is that I need to automate a cell to average the first 9 months of 2006, and then automatically change to 10 months and then to 11 months as time passes. I'm looking for a function that is something like: average first 9 values, so I can input a range of values and then a formula that will determine how many of those values to average. Does anyone know how I can accomplish this? If my explanation isn't clear, please let me know. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average the first nine numbers in a range.
One way:
Assuming range is B2:M2: =AVERAGE(OFFSET(B2,,,1, MONTH(TODAY())-1) Note that this won't work once January rolls around. IF A2 has 2006, then this should work: =AVERAGE(OFFSET(B2,,,1,MIN(12,DATEDIF(DATE(A2+1,1, 0),TODAY(),"M")))) though I'm pretty sure it's not the most efficient way In article , Chad wrote: I am trying to compare the average for the first nine months of 2007, to 2006; once october is over this will change to the first 10 months of 2007 and so on. I have an automated cell that counts the number of months that have passed in 2007, and another that takes the average of the the 2007 results. The problem is that I need to automate a cell to average the first 9 months of 2006, and then automatically change to 10 months and then to 11 months as time passes. I'm looking for a function that is something like: average first 9 values, so I can input a range of values and then a formula that will determine how many of those values to average. Does anyone know how I can accomplish this? If my explanation isn't clear, please let me know. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average the first nine numbers in a range.
I'll give an example of what the worksheet looks like to help clarify.
Date: Value: Average: 01/01/06 11500 ...... 01/15/06 12050 ....... 01/30/06 12750 12000 ........ 12/31/06 13500 13000 .......... 9/30/07 14000 12500 ........... So this is a rough example, but basically, every day from the beginning of 2006 until the currenty date would be in the far left column, and a corresponding value would be listed in each cell of the middle column. Then in the right column there is a monthly average calculated on the final day of each month. So, idealy I could average all the monthly averages calculated in 2007, which would be nine cells, then do some average function with a count function emedded in it, that would average the first nine numbers in the entire 2006 monthly average range. Any thoughts? "JE McGimpsey" wrote: One way: Assuming range is B2:M2: =AVERAGE(OFFSET(B2,,,1, MONTH(TODAY())-1) Note that this won't work once January rolls around. IF A2 has 2006, then this should work: =AVERAGE(OFFSET(B2,,,1,MIN(12,DATEDIF(DATE(A2+1,1, 0),TODAY(),"M")))) though I'm pretty sure it's not the most efficient way In article , Chad wrote: I am trying to compare the average for the first nine months of 2007, to 2006; once october is over this will change to the first 10 months of 2007 and so on. I have an automated cell that counts the number of months that have passed in 2007, and another that takes the average of the the 2007 results. The problem is that I need to automate a cell to average the first 9 months of 2006, and then automatically change to 10 months and then to 11 months as time passes. I'm looking for a function that is something like: average first 9 values, so I can input a range of values and then a formula that will determine how many of those values to average. Does anyone know how I can accomplish this? If my explanation isn't clear, please let me know. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two columns range of numbers need to list all numbers in the range | New Users to Excel | |||
Average first n numbers in a range (there may be less than n numbe | Excel Discussion (Misc queries) | |||
Statistical smoothing a range of numbers - match average more clos | Excel Worksheet Functions | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions |