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