I need to average specific dates across several years

[Mozzieman]

I would like to average each date across several years. Data is pretty simple, only two columns of rough data, the date and the number to average. Data spans 12 years and I want to average the same date across all 12 years. Everything I have tried comes out as an error of some kind. Columns are about 35000 rows, but I can condense it to a smaller number of rows fairly easily (figured that one out earlier) once I can figure out the average thing.

 

[Joe4]

Welcome to the Board!

OK, let's say that you have data in rows 2 through 700 and:
- column A has valid dates
- column B has the values you want to average
- cell D1 has a valid date that has the month/day you want (it doesn't matter what year you pick, just as long as it is a valid date)

The following formula would average all the values where the month/day part of the date in column A matches the month/day part of the date in cell D1:
=SUMPRODUCT(--(MONTH(A2:A700)=MONTH(D1)),--(DAY(A2:A700)=DAY(D1)),B2:B700)/SUMPRODUCT(--(MONTH(A2:A700)=MONTH(D1)),--(DAY(A2:A700)=DAY(D1)))

 

[Joe4]

Note that we can simplify this a little bit more by using the TEXT function like this:
=SUMPRODUCT(--(TEXT(A2:A700,"mmdd")=TEXT(D1,"mmdd")),B2:B700)/SUMPRODUCT(--(TEXT(A2:A700,"mmdd")=TEXT(D1,"mmdd")))

 

[Mozzieman]

Thanks, that worked like a charm.

 

[Joe4]

You are welcome.