correl function

Hi, hope you can help.
I have a spreadsheet with a primary input column A and secondary inputs B,C,D etc

I am using the correl function to work out the correlation between the primary input A against the secondary inputs B,C,D etc trying to identify the highest correlatiion.

My problem

Input A has values of either 1 or -1 and the secondary inputs B,C,D etc has inputs 1, -1 and 0. I need the function to ignor the 0 values and only work out the correlation when the secondary inputs in columns b,c,d etc have either a 1 or -1

A,B,C,D,E,F
1,-1,1,-1,0,1
1,1,1,1,0,0
-1,1,1,0,0,0

thank you

Hi

Is this the type of thing you are chasing?


=IF(COUNTIF(B1:B3,0)>0,"",CORREL($A$1:$A$3,B1:B3))


rylo

No , i may have not explained this very well, i want the calculation to be made but just considering the rows that have a 1 or -1 in the secondary input columns ie b,c,d,e,f etc so for eg when working out the corr between say the primary input A and a secondary input say column b if a 0 is in any row of column b ignor this in the correlation equation ie

a,b
1,0
-1,1
1,1
1,1
1,1

in the above the 1st row would be ignored as there is a 0 in column b but i still want the correlation equation to take place but in this case using rows 2-5

hope this explains it better
thank you again

Hi

How would you want to handle a situation where there was a 0 partway down the column?

a,b
1,1
1,1
1,0
1,1
-1,1

Build a comparable array for the non 0 entries, or based on the first 2 rows only???


rylo

hi thank you for your help, i would need to build an array as all non 0 rows must be included in the calculation

Perhaps the following as an array (sft ctrl and enter when finished typin gthe formula)

=CORREL((IF(D3:D9<>0,C3:C9,"")),(IF(D3:D9<>0,D3:D9,"")))

A word of caution I have had different results in the past with regression functions between excel 97 and 2003 depending how they treat blank values. So check that it works on machines your using. I only have 97 in this office

Regards

Dav