CODEWITHSUNDEEP
excelexcel-formulapearson-correlationvba
Davey
Davey

Reputation: 123

Using Correl Function in Excel for Varying Array Sizes

So the current setup of the problem at hand is that I have 4 columns, Employee ID, Category 1, Category 2, and Category 3. I need to find the correlation between Category 1 & Category 2, Category 1 & Category 3, and Category 2 & Category 3 for each Employee ID. The issue is that the array length for each Employee ID is different. Some employees will have 5 records, some employees will have 8 records to their ID. This problem would be simple if the Subtotal button had the CORREL function built into it given its grouping by feature.

How would I go about calculating the 3 correlation coefficients for each unique Employee ID? Excel function or VBA works

Upvotes: 0

Views: 2062

Answers (1)

zgirod
zgirod

Reputation: 4379

You need to use an array formula. Please see this screen shot of a sample situation:

enter image description here

As you can see the formula for cell G2 is: =CORREL(IF($A$2:$A$16=F2,$B$2:$B$16,""), IF($A$2:$A$16=F2,$C$2:$C$16,"")) That is saying if cell in column A matches your employeeId, include the cell in corresponding column in the array (Column B for the first IF and Column C for the second ID). After entering in the formula you need to make sure you hit Ctrl+Shift+Enter to tell excel you want to do an array formula or Command+Enter on a Mac.

You obviously need to modify the formula to fit the size of your data and you can copy that formula to any cells if you setup your $s correctly.

Upvotes: 1

Related Questions