title: Correlation in Excel Pivot Table
body: <p>I have a significant amount of data which I must analyse in Excel (this would be much easier in a DB, I realize). Each line item has a T/F indicator for it's membership in a given category and I would like to get a correlation of volume and price for each category across months. The quantity of data I am talking about is between 8-30k records at a time. We use Excel 2010 at my office</p>
  
  <p>Example data:</p>
  
  <pre><code>State | Cat.1 | Cat.2 | Cat.3 |    TimeStamp    | Volume | Price
   WA   |  TRUE | FALSE | FALSE | 01/31/13 12:00  | 113.1  | 35.64
   WA   |  TRUE | TRUE  | FALSE | 01/31/13 13:00  |  65.2  | 32.52
   FL   |  TRUE | FALSE | TRUE  | 01/31/13 02:00  |  78.9  | 36.37
   FL   |  TRUE | TRUE  | FALSE | 01/31/13 23:00  | 113.9  | 39.39
  </code></pre>
  
  <p>Ideally what I want is a pivot table that will correlate Volume and Price for a given combination of State, Cat.1, Year, and Month.</p>
  
  <p>Right now I have a Calculated field with formula: =correl(Volume,Price)
  However that field returns #DIV/0 for every scenario, but if I double click to view the applicable data, I can manually do the correl() and it works fine.</p>
  
  <p>Any help will be much appreciated!</p>