Correlation in Excel Pivot Table

Question

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

Example data:

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

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.

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.

Any help will be much appreciated!

Wild138 Wild138 · Accepted Answer · 2013-06-10T00:11:36

Not sure if I understand your question entirely but it sounds like you want to correlate based on the value of some of your other columns. I'm assuming therefore if you used autofilter on the header row and you could filter on the states and categories you wanted you could just correlate the visible values. You might need two extra columns with =MONTH( ) and =YEAR( ) with the timestamp.

The UDF i've written below will correlate only visible columns, so go to the VBA editor (Alt F11 ) and copy this, then enter the formular below and filter your columns

=CorrelVisibleCells(F1:F100,G1:G100)

assuming F and G are your volume and price columns

Public Function CorrelVisibleCells(aRange1 As Range, aRange2 As Range)
Dim aCorrelRange1() 
Dim aCorrelRange2() 
Dim rc As Long
Dim clCount As Long

Application.Volatile True  'This is a volatile function as changing the filter state    without changing the cell reference requires a recalc

rc = aRange1.Rows.Count
ReDim aCorrelRange1(rc) 'Largest they could be, will crop at the end
ReDim aCorrelRange2(rc)
clCount = 0
For i = 1 To rc
    If Not aRange1.Cells(i).EntireRow.Hidden Then  ' Can't use     SpecialCells(xlCellTypeVisible) in a UDF
        aCorrelRange1(clCount) = aRange1.Cells(i).Value
        aCorrelRange2(clCount) = aRange2.Cells(i).Value
        clCount = clCount + 1
    End If
Next
ReDim Preserve aCorrelRange1(clCount - 1)
ReDim Preserve aCorrelRange2(clCount - 1)

CorrelVisibleCells = WorksheetFunction.Correl(aCorrelRange1, aCorrelRange2) 'Get Excel to do the correlation on our new arrays

End Function

Correlation in Excel Pivot Table

1 Answers