Array Correlation Coefficient formula with Multiple Criteria/Variables

[smittyb]

Hi, i'm having an issue finding a correlation coefficient from 2 datasets (arrays) where the data meets 2 or 3 conditions.

i have 2 sheets 'Primary' and 'Twin'. On both sheets i have variables (text) in columns A, J, and K and the data to correlate (numbers) in column F.

i want to be able to correlate the data in the F columns where the variables in columns A, J, and K are equal to the values i select from dropdowns which i have in cells B2, D2, and AD1 on a seperate sheet (so there can be several different combinations of variables).

while the number of records on each sheet that match the selected variable combination will be similar they will not always be the same (if ever).

i know that i can add a condition to the array formula for just one criteria (per sheet) like so...

=IFERROR(CORREL(IF($B$2<>"",IF(Primary!$A$2:$A$5001=$B$2,Primary!$F$2:$F$5001),Primary!$F$2:$F$5001),IF($D$2<>"",IF(Twin!$A$2:$A$5001=$D$2,Twin!$F$2:$F$5001),Twin!$F$2:$F$5001)),"*")

the false argument in the above simply correlates all the data if the variable is set to null.

the above works but if i try to add a second variable it fails with a value not available error like so...

=IFERROR(CORREL(IF($B$2<>"",IF(Primary!$A$2:$A$5001=$B$2,IF(Primary!$K$2:$K$5001=AD1,Primary!$F$2:$F$5001)),IF(Primary!$K$2:$K$5001=AD1,Primary!$F$2:$F$5001)),IF($D$2<>"",IF(Twin!$A$2:$A$5001=$D$2,IF(Twin!$K$2:$K$5001=AD1,Twin!$F$2:$F$5001)),IF(Twin!$K$2:$K$5001=AD1,Twin!$F$2:$F$5001))),"*")

interestingly the above will give a value for some variable combinations but not others. i've checked and there are no null values or divisions by zero errors. once you add the second varialbe all of a sudden the number of records being correlated on each seem to have to be equal. it's just not playing the game!

anyone got any clue about the best way to tackle this?

Cheers,