I need a formula for a nine year data set

[Redpoll19]

I have nine years of daily data with a single value for each day. I need a formula that will give me the median for each calendar day of all nine years. That is, I need the median for nine values on Jan 1, 2,...,Dec 31.

Thanks for any help.

 

[schielrn]

Believe this should work, adjust it as needed, but 1 and 5 in there are for January 5th, but you could make those based on cell references.

=MEDIAN(IF((MONTH(A1:A10000)=1)*(DAY(A1:A10000)=5),B1:B10000))

This needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

 

[BobUmlas]

put the calendar dates for one year in a column, say E and name the dates "Rg", then ctrl/shift/enter this formula:

=MEDIAN(IF(MONTH(rg)=MONTH(E2),IF(DAY(rg)=DAY(E2),OFFSET(rg,,1),""),""))

and fill down

 

[MrRajKumar]

Hello,

Enter the calendar date in C1, then try this,

=MEDIAN(IF(TEXT(A1:A100,"mmmd")=TEXT(C1,"mmmd"),B1:B100))

Confirmed with Control+Shift+Enter.