average by date

[chumley]

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.

 

[Peo Sjoblom]

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]

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

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

 

[Peo Sjoblom]

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