monthly median based on daily values

[bloodybrit90]

Hey guys,

I would like to find the monthly median price of daily transactions. For an average I can use a pivot table, but it does not have the median function build in. I have calculated the daily median value using an array formula.

=IF(A10>=1,MEDIAN(IF('textexport(1)'!$O:$O=Sheet3!B10,'textexport(1)'!$P:$P)),NA())
column A has the number of sales and column B has all the days. What is the best way for me to get the median of all the data per month?

Cheers

 

[barry houdini]

If C2 is text then although Excel will let you re-format it that doesn't have any actual effect. Can you re-enter the data in column C, what does it look like?

 

[bloodybrit90]

I changed my date to 1/01/12.....2/01/12... and everything worked. Thanks for the help Barry.

 

[runriverrun]

This is great. Assume you had several decades of data. How would you determine the median for a given month and given year?

 

[Aladin Akyurek]

This is great. Assume you had several decades of data. How would you determine the median for a given month and given year?

Given...

Data: A2:B400, with A2:A400 housing true dates and B2:B400; and

E2: 1-Jan-2000 (a first day date of a month/year of interest)

F2, control+shift+enter, not just enter:

=MEDIAN(IF($A$2:$A$400-DAY($A$2:$A$400)+1=$E2,$B$2:$B$400))

A better option is a pivot table, grouped by month followed by year.