how to do median by month for daily flow data in Excel 2002?

[MCMA]

Been struggling with this for a bit now....

Using Excel 2002 in Win2000Pro

I have five years worth of daily river flow data. Column 1 is in dd/mm/yyyy and column 2 is numeric data. The data is not necessarily continuous - data from some days has been lost. Those missing days have been removed from the daatset.

What I'm after is the calculation of median by month for each month of those five years. I've been trying to select a range by month and have had no joy....

if only median was available in sub-totals....

 

[Aladin Akyurek]

Re: how to do median by month for daily flow data in Excel 2

Been struggling with this for a bit now....

Using Excel 2002 in Win2000Pro

I have five years worth of daily river flow data. Column 1 is in dd/mm/yyyy and column 2 is numeric data. The data is not necessarily continuous - data from some days has been lost. Those missing days have been removed from the daatset.

What I'm after is the calculation of median by month for each month of those five years. I've been trying to select a range by month and have had no joy....

if only median was available in sub-totals....

Let A2:B40 house the data.

Let C2 house the first day date of the month/year of interest, e.g., 1-Jan-2002.

=MEDIAN(IF($A$2:$A$40-DAY($A$2:$A$40)+1=C2,$B$2:$B$40))

which must be confirmed with control+shift+enter, not just with enter.

 

[MCMA]

All sorted! Sirrah, my thanks to you.