# I need a formula for a nine year data set

#### Redpoll19

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### schielrn

##### Well-known Member
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

##### Well-known Member
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

##### Active Member
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.

Replies
3
Views
22
Replies
3
Views
62
Replies
11
Views
135
Replies
2
Views
102
Replies
7
Views
54