Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average by date
In Row 1 of my spreadsheet are dates in MMM-YY format. I Row 2 are different
values. What I want to do is maintain an average, by year, in another cell on another worksheet that changes every time a new month of data is added. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average by date
All Excel dates that are seen as dates are always the whole enchilada, so
even if you have a custom format displaying the dates as mmm-yy they will always have days as well or else they are not dates. Assuming that when you select a date you will see the full date in the formula bar then you can use =AVERAGE(IF(YEAR(A2:A30)=2007,B2:B30)) for 2007 needs to be entered with ctrl + shift & enter -- Regards, Peo Sjoblom "chumley" wrote in message ... In Row 1 of my spreadsheet are dates in MMM-YY format. I Row 2 are different values. What I want to do is maintain an average, by year, in another cell on another worksheet that changes every time a new month of data is added. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average by date
"Peo Sjoblom" wrote: All Excel dates that are seen as dates are always the whole enchilada, so even if you have a custom format displaying the dates as mmm-yy they will always have days as well or else they are not dates. Assuming that when you select a date you will see the full date in the formula bar then you can use =AVERAGE(IF(YEAR(A2:A30)=2007,B2:B30)) for 2007 needs to be entered with ctrl + shift & enter -- Regards, Peo Sjoblom "chumley" wrote in message ... In Row 1 of my spreadsheet are dates in MMM-YY format. I Row 2 are different values. What I want to do is maintain an average, by year, in another cell on another worksheet that changes every time a new month of data is added. Thanks very much; it was very helpful. I modified the formula somewhat, since the data I'm working with is in rows rather than columns. I also changed the range reference to include the whole column. So, the formula I wound up with was =AVERAGE(IF(YEAR(1:1)=2007,2:2)) and that works great. However, I then tried to alter it for use when the data is in columns, like this: =AVERAGE(IF(YEAR(A:A)=2007,B:B)) and I get a #NUM error message. When I specify a range other than the entire column, like this: =AVERAGE(IF(YEAR(A1:A36)=2005,B1:B36)) it works just fine. Of course, if I have to, I could just specify a column range that will accomodate all the data I'll ever put in there, but I'm confused, and curious, about why it will work with rows but not columns. If it helps at all, the row data looks like this: Nov-05 Dec-05 Jan-06 Feb-06 15 15 30 30 and the Column data looks like this: Nov-05 15 Dec-05 15 Jan-06 30 Feb-06 30 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average by date
You would need to use B1:B65535, rows need to be specified when it comes to
array formulas although I think it has been fixed in 2007. Not that I am switching to 2007, to many other drawbacks -- Regards, Peo Sjoblom "chumley" wrote in message ... "Peo Sjoblom" wrote: All Excel dates that are seen as dates are always the whole enchilada, so even if you have a custom format displaying the dates as mmm-yy they will always have days as well or else they are not dates. Assuming that when you select a date you will see the full date in the formula bar then you can use =AVERAGE(IF(YEAR(A2:A30)=2007,B2:B30)) for 2007 needs to be entered with ctrl + shift & enter -- Regards, Peo Sjoblom "chumley" wrote in message ... In Row 1 of my spreadsheet are dates in MMM-YY format. I Row 2 are different values. What I want to do is maintain an average, by year, in another cell on another worksheet that changes every time a new month of data is added. Thanks very much; it was very helpful. I modified the formula somewhat, since the data I'm working with is in rows rather than columns. I also changed the range reference to include the whole column. So, the formula I wound up with was =AVERAGE(IF(YEAR(1:1)=2007,2:2)) and that works great. However, I then tried to alter it for use when the data is in columns, like this: =AVERAGE(IF(YEAR(A:A)=2007,B:B)) and I get a #NUM error message. When I specify a range other than the entire column, like this: =AVERAGE(IF(YEAR(A1:A36)=2005,B1:B36)) it works just fine. Of course, if I have to, I could just specify a column range that will accomodate all the data I'll ever put in there, but I'm confused, and curious, about why it will work with rows but not columns. If it helps at all, the row data looks like this: Nov-05 Dec-05 Jan-06 Feb-06 15 15 30 30 and the Column data looks like this: Nov-05 15 Dec-05 15 Jan-06 30 Feb-06 30 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find an average date | Excel Worksheet Functions | |||
Running Average to Date | Excel Discussion (Misc queries) | |||
Average by Date | Excel Worksheet Functions | |||
average for new date | Excel Discussion (Misc queries) | |||
Average to date | Excel Worksheet Functions |