title: Find daily percentile for large data set of irregular data
body: <p>I have a very large data set (> 1 million rows) with percentiles that need to be calculated for all of the same day (e.g., all Jan 1, all Jan 2, ..., all Dec 31).  There are many rows of the same year, month and day with different data.  Below is an example of the data:</p>
  
  <pre><code>    Year  Month  Day  A  B  C  D
      2007  Jan    1    1  2  3  4
      2007  Jan    1    5  6  7  8
      2007  Feb    1    1  2  3  4
      2007  Feb    1    5  6  7  8
      .
      .
      2010  Dec    30   1  2  3  4
      2010  Dec    30   5  6  7  8
      2010  Dec    31   1  2  3  4
      2010  Dec    31   5  6  7  8
  </code></pre>
  
  <p>So to calculate the 95th percentile for Jan 1, it would need to include all Jan 1 for all years (e.g., 2007-2010)  and for all columns (A, B, C and D).  This is then done for all Jan 2, Jan 3, ..., Dec 30 and Dec 31.  This can easily be done with small data sets in Excel by using nested if statements; e.g., ={PERCENTILE(IF(Month($B$2:$B$1000000)="Jan",IF(Day($C$2:$C$1000000)="1",$D$2:$G$1000000)),95%)}</p>
  
  <p>The percentiles could then be added to a a new data table containing only the month and days:</p>
  
  <pre><code>    Month  Day  P95  P05
      Jan    1
      Jan    2
      Jan    3
      .
      .
      Dec    30
      Dec    31
  </code></pre>
  
  <p>Then using the percentiles, I need to evaluate whether each data value in column names A, B, C and D for their respective date (e.g., Jan 1) is larger than P95 or smaller than P05.  Then new columns could be added to the first data table containing 1 or 0 (1 if larger or smaller, 0 if not larger or smaller than the percentiles):</p>
  
  <pre><code>    Year  Month  Day  A  B  C  D  A05  B05  C05  D05  A95  B95  C95  D95
      2007  Jan    1    1  2  3  4  1    0    0    0    0    0    0    0
      2007  Jan    1    5  6  7  8  0    0    0    0    0    0    1    1
      .
      .
      2010  Dec    31   5  6  7  8  0    0    0    0    0    0    0    1
  </code></pre>