Median question

[rezzor]

A have a bunch of data where I want to calculate the median of the 'section' for each 'index date'. For example, for the section of Allergy I would want the median for 01/01/08 by taking the median of days delay for that index date and so on for each index date and section combination.

I initially tried to do this in a pivot table but after reading up, I've realized that I need some sort of If median array but can't figure out how to do it. Is it possible to do this? TIA






<table x:str="" style="border-collapse: collapse; width: 258pt;" border="0" cellpadding="0" cellspacing="0" width="343"><col style="width: 67pt;" span="2" width="89"> <col style="width: 68pt;" width="90"> <col style="width: 56pt;" width="75"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 67pt;" height="17" width="89">Section
</td> <td style="width: 67pt;" width="89">Lastname</td> <td style="width: 68pt;" width="90">DAYS DELAY</td> <td style="width: 56pt;" width="75">INDEX DAY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>a</td> <td x:num="" align="right">5</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>b</td> <td x:num="" align="right">9</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>c</td> <td x:num="" align="right">2</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>d</td> <td x:num="" align="right">5</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>e</td> <td x:num="" align="right">4</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>a</td> <td x:num="" align="right">45</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>b</td> <td x:num="" align="right">21</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>c</td> <td x:num="" align="right">2</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>d</td> <td x:num="" align="right">1</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Allergy</td> <td>e</td> <td x:num="" align="right">4</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>a</td> <td x:num="" align="right">10</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>b</td> <td x:num="" align="right">5</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>c</td> <td x:num="" align="right">3</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>d</td> <td x:num="" align="right">6</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>e</td> <td x:num="" align="right">2</td> <td class="xl22" x:num="39448" align="right">01/01/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>a</td> <td x:num="" align="right">10</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>b</td> <td x:num="" align="right">5</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>c</td> <td x:num="" align="right">3</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>d</td> <td x:num="" align="right">6</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Derm</td> <td>e</td> <td x:num="" align="right">2</td> <td class="xl22" x:num="39449" align="right">01/02/08</td> </tr> </tbody></table>

 

[Cbrine]

OK, this should work.

=SUMPRODUCT((A4:A23="Allergy")*(D4:D23=DATEVALUE("01/01/2008"))*(C4:C23))/SUMPRODUCT(--(A4:A23="Allergy"),--(D4:D23=DATEVALUE("01/01/2008")))

HTH
Cal

 

[rezzor]

Thank you Cal, I will try this.

 

[Aladin Akyurek]

Try...
Control+shift+enter, just enter:

=MEDIAN(
   IF(SectionRange=X2,
   IF(DateRange=Y2,
     DelayRange)))

where X2 houses a section of interest and Y2 a date of interest.

 

[rezzor]

Aladin - many thanks, that worked well.

I ended up using it like this and dragging it down:
=MEDIAN(IF($C$2:$C$100=C2,IF($Q$2:$Q$100=Q2,$F$2:$F$100)))

that way it calculated for all sections and dates.