average by date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chumley - 06 May 2008 15:50 GMT

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.

Reply to this Message

Peo Sjoblom - 06 May 2008 16:13 GMT

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

Signature

Regards,

Peo Sjoblom

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

Reply to this Message

chumley - 09 May 2008 18:13 GMT

> 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
[quoted text clipped - 12 lines]
> > 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 to this Message

Peo Sjoblom - 09 May 2008 18:28 GMT

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

Signature

Regards,

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
[quoted text clipped - 50 lines]
> Jan-06 30
> Feb-06 30

Reply to this Message